topic Re: Problem with many to many lookup in DAX Commands and Tips

Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 17:12:02 GMT

I am having trouble creating a filter column based on a calcuation in another table. It is something I have done several times, but it just isn't working here.

Here is the basic structure of the reference table.

ID	End_Date	Filter-2
143	19.02.22 00:00	UNKNOWN
448	10.10.14 00:00	NO
448	21.03.19 00:00	NO
1369	23.07.14 00:00	UNKNOWN
36464	30.09.19 00:00	UNKNOWN
36464	19.02.22 00:00	NO
36464	30.09.20 00:00	NO

And here is the table where I need to create a filter column.

ID	Date	Filter-1	Filter-2
143	14.07.15 00:00	L3	19.02.22 00:00
143	26.11.14 00:00	L3	19.02.22 00:00
143	08.11.11 00:00	L3
143	29.11.11 00:00	L3
143	08.05.15 00:00	L3	19.02.22 00:00
143	11.04.11 00:00	L2
143	09.05.12 00:00	L3
143	27.11.14 00:00	L2
143	05.04.12 00:00	L3
143	26.11.13 00:00	L3	19.02.22 00:00
448	11.04.11 00:00	L3
448	11.04.11 00:00	L2
448	21.08.14 00:00	L2
448	24.11.15 00:00	L3
448	15.07.14 00:00	L2
448	15.07.14 00:00	L3
448	21.08.14 00:00	L3
1369	08.11.11 00:00	L3
1369	29.11.11 00:00	L3	23.07.14 00:00
1369	08.05.12 00:00	L2	23.07.14 00:00
1369	04.04.12 00:00	L3	23.07.14 00:00
36464	14.01.21 00:00	L3
36464	04.10.17 00:00	L3
36464	10.01.20 00:00	L3
36464	20.02.21 00:00	L3
36464	04.10.18 00:00	L3	30.09.19 00:00
36464	02.10.20 00:00	L3
36464	04.06.21 00:00	L3
36464	27.02.18 00:00	L2	30.09.19 00:00
36464	08.06.20 00:00	L3
36464	05.06.19 00:00	L3	30.09.19 00:00
36464	28.09.21 00:00	L3
36464	14.01.22 00:00	L1	19.02.22 00:00
36464	04.10.18 00:00	L2	30.09.19 00:00
36464	15.01.18 00:00	L3
36464	20.04.17 00:00	L2

As you can see, I had no problem creating the Filter-1 column (from a third table), using basically the same strategy. But the Filter-2 column has multiple blanks. I have played around with it in different ways (e.g., MIN(), MAX(), etc.), and it does change which rows return the correct value, but there are always several blanks.

Here is the code I am using:

Filter-2 = Var End_Date = CALCULATE(MIN('Table1'[End_Date]), FILTER('Table1', 'Table1'[ID] = 'Table2'[ID] && 'Table1'[End_Date] >= 'Table2'[Date]) ) Var Filter_2 = CALCULATE(SELECTEDVALUE('Table1'[Filter-2]), FILTER( 'Table1', 'Table1'[ID] = 'Table2'[ID] && 'Table1'[End Date] = End_Date) ) RETURN Filter_2

Any ideas?

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 08:51:58 GMT

Note that in the second table above, I was testing to see if the correct end_date was being returned. The correct Filter_2 is returned in the exact same cells.

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 10:56:42 GMT

Hi Ennis,

Are the tables connected with Many-Many realtionship?

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 11:04:50 GMT

No, no connection at all yet. But there is also no connection to the third table with the Filter-1 data.

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 11:20:52 GMT

It is very clear that Filter-2 in the 2nd table is working only when there is only one value in table 1. Otherwise it returns a blank.
I believe it is more feasible to use relationships and utilize REALTEDTABLE to grap the table of related values, then it would much easier to get the max or min or whatever is required by your calculation.
By the way , I can only see two tables. Where is the third one?

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 11:23:04 GMT

Maybe I need to try MINX() to retrieve the end_date?

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 11:39:49 GMT

ID 143 and 1369 only have one row in the reference table, yet they still return several blanks. 36464, on the other hand, has three rows with three different values for the [End Date] and two for [FILTER-2]. Yet returns some of the correct results for two of the rows and nothing for the other.

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 11:41:20 GMT

FILTER-1 works properly. So I have not included the reference table or code to get that. But the situations is identical conceptually.

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 11:53:07 GMT

Can you share a sample file?

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 15:07:38 GMT

Thanks for the offer. But it is institutional data that cannot be shared. In the meantime, I have found a workaround. Very strange, but if I bring the F-Filter2 column into the third (not shown) calculated table where F-Filter 1 is in turn calculated, I have no problem looking up F-Filter2 from that table. There must be some sort of interdependence caused by calculations that is causing this problem. But I will leave this question unsolved in case somewhat out there can figure out why the direct lookup doesn't work...

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 16:00:01 GMT

SELECTEDVALUE returns blank incase multiple values were found. Maybe you have records having same ID and date.

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 16:05:16 GMT

It is unlikely that two entries for the same ID have the same end date, given the nature of the data. Even if there are one or two such occurences in the database (which, again, is unlikely), that would not explain the blanks shown above...

But I did think of that, which is why I tried MAX() and MIN() to test it out. The blanks/results ended up in different cells, which is strange.

The weirdest thing is that I have no problem pulling the data into and then from the calculated table for Filter-1. The code is different, but basically works the same way...

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 16:54:43 GMT

It is the context transition. CALCULATE converts the row into a filter. If the date in table 2 is missing in Table 1 then the date in the filter context is empty and CALCULATE returns blank.

Re: Problem with many to many lookup

MJEnnis — Sat, 19 Feb 2022 17:11:15 GMT

All rows in Table 1 have an End Date. And the VAR End_Date calculated in the calculated column of Table 2 matches the date in Table1 (as seen in the copied entries above). It is just that CALCULATE is retrieving the match for some rows in Table 2 and not for others. If I replace SELECTEDVALUE with MIN or MAX, then it retrieves the correct date for different rows. I think for some reason CALCULATE(MIN('Table1'[End_Date])... is not working the way I expect it too.

Re: Problem with many to many lookup

tamerj1 — Sat, 19 Feb 2022 18:06:14 GMT

In the first CCALCULATE, try to store

Table2'[Date]

in a variable outside CALCULATE

Re: Problem with many to many lookup

tamerj1 — Mon, 21 Feb 2022 10:51:27 GMT

Hi MJEnnis
I was working on something and I encountered a situation that reminded me of your problem. My issue was quite different but it reminded me that small thngs can become big issues. So I went back to your sample data, copied it into excel and uploaded it into PowerBi.
I wanted to know what would happen if I have only two similar dates in table one.
I changed the code replacing SELECTEDVALUE with COUNTROWS just to know how many rows are returned. If more thant one then the outcome of selected value would be "blank". This is what I got

Actually all the Table 2 dates which are less than or equal to Table 1 date (either min or max depending on the function you used) will resuly in blank in the calculated column as they return a table of 2 rows
Only one duplicated date resulted in around 10 blanks in an extremely tiny data set. The actual data set will result in a huge number of blanks as happend with you.

Re: Problem with many to many lookup

v-chenwuz-msft — Wed, 23 Feb 2022 06:52:41 GMT

Hi MJEnnis ,

We do not use selectedvalue when create column but [column name] directly. And if we want get filter result form table1 where table1 id = table2 id, we need to use eariler() function.

So please try the following code to get the min date depend on ID from table 1:

Filter-2 = CALCULATE( MIN( 'Table1'[End_Date] ), FILTER( 'Table1', 'Table1'[ID] = EARLIER( 'Table2'[ID] ) && 'Table1'[End_Date] >= EARLIER( 'Table2'[Date] ) ) )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem with many to many lookup

MJEnnis — Thu, 24 Feb 2022 21:31:23 GMT

Thanks a lot for the suggestion. The code you propose doesn't really produce the result I have explained above. Moreover, I managed to get it to work with SELECTEDVALUE() and without EARLIER(). Will see about using the column name directly in the future.

Re: Problem with many to many lookup

MJEnnis — Thu, 24 Feb 2022 22:20:44 GMT

Yes, something like this was going on. I do not know that the number of rows returned in your code is the (only) issue, though. As it clearly does not not account for all of the blanks. ID 143, for instance, only has one entry in the reference table.

To solve it, I had to add two other columns column to the filters , also refering to the start date (not shown above). I thought that calculating the MIN('Table1'[End_Date]) for each row was a crafty way to account for all possibilities, but it just wouldn't work. Not even for ID 143, even though the only possible date for each and every row is 19.02.22 00:00. Anyway, this is what eventually worked. (A couple of the conditions are not necessary yet, but may become necessary in the future. So they are there just to cover all my bases.)

NewFilter = Var Lang = LOOKUPVALUE('TABLE3'[Language], 'TABLE3'[Event ID], LOOKUPVALUE('TABLE4'[Event ID], 'TABLE4'[Session ID], 'TABLE3'[Session ID]) ) Var EndDate1 = Calculate(SELECTEDVALUE(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] >= 'TABLE2'[Date], ‘TABLE1’[Start_Date] <= 'TABLE2'[Date])) ) Var EndDate2 = Calculate(SELECTEDVALUE(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] > 'TABLE2'[Date]) ) Var EndDate3 = Calculate(MAX(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] > 'TABLE2'[Date]) ) Var EndDate4 = Calculate(SELECTEDVALUE(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] > 'TABLE2'[Date] && ‘TABLE1’[End_Date] <> EndDate3) ) Var EndDate5 = Calculate(MIN(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] > 'TABLE2'[Date]) ) Var EndDate6 = Calculate(SELECTEDVALUE(‘TABLE1’[End_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] > 'TABLE2'[Date] && ‘TABLE1’[End_Date] <> EndDate3 && ‘TABLE1’[End_Date] <> EndDate5) ) Var EndDate = IF(NOT(ISBLANK(EndDate1)), EndDate1, IF(NOT(ISBLANK(EndDate2)), EndDate2, IF(NOT(ISBLANK(EndDate4)), Enddate4, IF(NOT(ISBLANK(EndDate5)), Enddate5, IF(NOT(ISBLANK(EndDate6)), Enddate6, IF(NOT(ISBLANK(EndDate3)), Enddate3, BLANK() )))))) Var StartDate1 = Calculate(Max(‘TABLE1’[Start_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] > 'TABLE2'[Date], ‘TABLE1’[Start_Date] < 'TABLE2'[Date])) ) Var StartDate2 = Calculate(MIN(‘TABLE1’[Start_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] > 'TABLE2'[Date], ‘TABLE1’[Start_Date] < 'TABLE2'[Date])) ) Var StartDate3 = Calculate(SELECTEDVALUE(‘TABLE1’[Start_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] > 'TABLE2'[Date], ‘TABLE1’[Start_Date] < 'TABLE2'[Date]) && ‘TABLE1’[Start_Date] <> StartDate1 && ‘TABLE1’[Start_Date] <> StartDate2) ) Var StartDate4 = Calculate(Min(‘TABLE1’[Start_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] > 'TABLE2'[Date], ‘TABLE1’[Start_Date] > 'TABLE2'[Date])) ) Var StartDate5 = Calculate(Max(‘TABLE1’[Start_Date]), FILTER(‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && AND(‘TABLE1’[End_Date] > 'TABLE2'[Date], ‘TABLE1’[Start_Date] > 'TABLE2'[Date])) ) Var StartDate = IF(NOT(ISBLANK(StartDate1)), StartDate1, IF(NOT(ISBLANK(StartDate2)), StartDate2, IF(NOT(ISBLANK(StartDate3)), StartDate3, IF(NOT(ISBLANK(StartDate4)), StartDate4, IF(NOT(ISBLANK(StartDate5)), StartDate5, BLANK() ))))) Var NewFilter = CALCULATE(SELECTEDVALUE(‘TABLE1’[True Beginner L3]), FILTER( ‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[End_Date] = EndDate) ) Var NewFilter2 = CALCULATE(SELECTEDVALUE(‘TABLE1’[True Beginner L3]), FILTER( ‘TABLE1’, ‘TABLE1’[ID] = 'TABLE2'[ID] && ‘TABLE1’[Language] = Lang && ‘TABLE1’[Start_Date] = StartDate) ) Var NewFilter3 = IF(NOT(ISBLANK(NewFilter)), NewFilter, NewFilter2 ) RETURN NewFilter3