So here’s the problem I faced.
I created a Power BI data set that had many tables in it, but I’ll focus on two tables here and ignore the rest. Table1 was a Fact table that contained fact records by Year and Week number. Table2 was a Calendar table that listed Dates and the corresponding Year and Week number for each. I’ll refer to Year and Week number simply as Week number from here on in.
The join between these two tables was a many-to-many, because each Week number appeared multiple times in both tables. Here’s a representative visual of these tables and the join between them.
This was all fine until I started trying to use some DAX functions that didn’t work as expected with a many-to-many join in the mix (and in deed Power BI does warn us about using these joins). It’s relevant to mention at this point that some of the other tables in the data set, which I’m ignoring here, had one-to-one & one-to-many joins between them; and some of those tables joined to these tables, so it was a mixed bag of joins with different cardinalities.
To overcome the unexpected DAX results I was seeing, I decided that I needed to change the join between Table1 and Table2 to join on Date, and in doing so make it a many-to-one join. But how to do this?
My Excel brain kicked in and I decided that I effectively wanted to VLOOKUP the Week number from Table1 in Table2, and then add a Date to each Week number in Table1 from Table2. This is probably better explained as a picture.
There is no VLOOKUP function in DAX, the nearest equivalent is LOOKUPVALUE. So, I gave this a shot and created a new column in Table1 using the following DAX.
Week Start Date lookup = LOOKUPVALUE(‘Table2 (Calendar)'[Date],’Table2 (Calendar)'[Year and Week Number],’Table1 (Fact)'[Year and Week Number])
The first LOOKUPVALUE parameter in the above is the column value you want to return. The second parameter is the column value you’re searching for. The third parameter is the column you’re searching for the second parameter in.
However, I very quickly realised that this wouldn’t work as each Week number in Table2 had multiple Dates against it (there’s 7 dates in a week of course). And indeed when I tried to add this column to Table1 Power BI gave me a kind error – it really should have said are you stupid! It was for this exact reason that I knew the RELATED function wouldn’t work, a function I’ve used many times before but in one-to-one and many-to-one join scenarios only.
My natural next thought was to return the first date from from Table2 for each Week number, so I tried altering the DAX I just created to:
Week Start Date lookup = LOOKUPVALUE(FIRSTDATE(‘Table2 (Calendar)'[Date]),’Table2 (Calendar)'[Year and Week Number],’Table1 (Fact)'[Year and Week Number])
However, it seems you can’t add functions like FIRSTDATE to the return value parameter in LOOKUPVALUE. So at this point I decided I had to calculate the Start Date for each Week number in Table2 first, and then return this in my LOOKUPVALUE in Table1 instead. I therefore created a new column in Table2 using similar DAX to the above.
Week Start Date = CALCULATE(FIRSTDATE(‘Table2 (Calendar)'[Date]),ALLEXCEPT(‘Table2 (Calendar)’,’Table2 (Calendar)'[Year and Week Number]))
I now had the start date for each Week number in Table2. I could now tweak my LOOKUPVALUE in Table1 to return this date, which means for each Week number in Table1 only one date is returned (and not in effect 7 as before). Below is the updated DAX for the LOOKUPVALUE column in Table1.
Week Start Date lookup = LOOKUPVALUE(‘Table2 (Calendar)'[Week Start Date],’Table2 (Calendar)'[Year and Week Number],’Table1 (Fact)'[Year and Week Number])
I now had what I needed to achieve a many-to-one join, a Date column (Week Start Date lookup) in Table1 that could be joined to the Date column in Table2.
At this point the DAX calculations I’d created at the very start of this exercise started behaving as excpected – RESULT!
The other nice feature here is the LOOKUPVALUE column in Table1 works even if there’s no physical join between tables 1 and 2. So LOOKUPVALUE can be used in some cases to simply your data set.
We hope sharing this with you has proved useful. Please do post any questions or comments you have below, we’d love to hear from you.