As always Paula this is a great resource, when I started out on powerpivot I always got that 'Relationships may be needed' warning and could never figure it out because it would not let me set up the links. Now after using Power pivot for so long its natural for me to work with bridging tables.
Nice sharing of workbooks to practice with. Cheers!
all of the data where I work ends up many to many, so I use bridging tables a lot and it brings all the benefits of being able to analyze different tables against each other. In power bi you can use bi-directional filtering and relationships but lets not confuse power pivot users. Of all the tricks you shared, learning to use CALCULATE is probably the most important, its just the magic of magic.
Thanks for this post @theexcelclub and the chance to do these learn and earn activities. Im hoping to earn enough steem from them to buy your premium courses. this is such a fantastic idea.
CALCULATE rocks and when I am doing some DAX posts, I will for sure cover it more. the rule of thumb, if it doesn't work, wrap calculate around it and most of the time it will solve your problem :-)
So the join, or foreign key table would be 'date'+'product' - 60 rows if there were 15 dates and 4 products. Dunno, other examples i've seen online use the same approach as you :)
As always Paula this is a great resource, when I started out on powerpivot I always got that 'Relationships may be needed' warning and could never figure it out because it would not let me set up the links. Now after using Power pivot for so long its natural for me to work with bridging tables.
Nice sharing of workbooks to practice with. Cheers!
hi @dernan, I'm glad you enjoyed the post. thanks for taking part in the learn and earn activity
all of the data where I work ends up many to many, so I use bridging tables a lot and it brings all the benefits of being able to analyze different tables against each other. In power bi you can use bi-directional filtering and relationships but lets not confuse power pivot users. Of all the tricks you shared, learning to use CALCULATE is probably the most important, its just the magic of magic.
Thanks for this post @theexcelclub and the chance to do these learn and earn activities. Im hoping to earn enough steem from them to buy your premium courses. this is such a fantastic idea.
CALCULATE rocks and when I am doing some DAX posts, I will for sure cover it more. the rule of thumb, if it doesn't work, wrap calculate around it and most of the time it will solve your problem :-)
Very cool.
I had to have a go at this one because I was hopeful there would be a way of having the Date/Product joined as a composite key. Apparently not!
This is a decent workaround though, thanks Paula!
oh that's an interesting take, it makes a lot of sense and sounds almost plausible - to the extent you have given me an idea!!!!
Can you make derived tables?
So the join, or foreign key table would be 'date'+'product' - 60 rows if there were 15 dates and 4 products. Dunno, other examples i've seen online use the same approach as you :)
its works in sql and I think with a little DAX magic you could get it to work.
When I was hunting for help I saw DAX mentioned but you know, I got things to do and people to see :P
Thank you for the tutorial paula.
I would be lost without you.
you are most welcome
You got a 100.00% upvote from @spydo courtesy of @theexcelclub! We offer 100% Payout and Curation. Thank you.