Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MJEnnis
Helper V
Helper V

Problem with many to many lookup

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.

 

IDEnd_DateFilter-2
14319.02.22 00:00UNKNOWN
44810.10.14 00:00NO
44821.03.19 00:00NO
136923.07.14 00:00UNKNOWN
3646430.09.19 00:00UNKNOWN
3646419.02.22 00:00NO
3646430.09.20 00:00NO

 

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

 

IDDateFilter-1Filter-2
14314.07.15 00:00L319.02.22 00:00
14326.11.14 00:00L319.02.22 00:00
14308.11.11 00:00L3 
14329.11.11 00:00L3 
14308.05.15 00:00L319.02.22 00:00
14311.04.11 00:00L2 
14309.05.12 00:00L3 
14327.11.14 00:00L2 
14305.04.12 00:00L3 
14326.11.13 00:00L319.02.22 00:00
44811.04.11 00:00L3 
44811.04.11 00:00L2 
44821.08.14 00:00L2 
44824.11.15 00:00L3 
44815.07.14 00:00L2 
44815.07.14 00:00L3 
44821.08.14 00:00L3 
136908.11.11 00:00L3 
136929.11.11 00:00L323.07.14 00:00
136908.05.12 00:00L223.07.14 00:00
136904.04.12 00:00L323.07.14 00:00
3646414.01.21 00:00L3 
3646404.10.17 00:00L3 
3646410.01.20 00:00L3 
3646420.02.21 00:00L3 
3646404.10.18 00:00L330.09.19 00:00
3646402.10.20 00:00L3 
3646404.06.21 00:00L3 
3646427.02.18 00:00L230.09.19 00:00
3646408.06.20 00:00L3 
3646405.06.19 00:00L330.09.19 00:00
3646428.09.21 00:00L3 
3646414.01.22 00:00L119.02.22 00:00
3646404.10.18 00:00L230.09.19 00:00
3646415.01.18 00:00L3 
3646420.04.17 00:00L2 

 

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?

18 REPLIES 18
v-chenwuz-msft
Community Support
Community Support

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.

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.

tamerj1
Super User
Super User

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. 

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.

MJEnnis
Helper V
Helper V

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.

Hi Ennis,

Are the tables connected with Many-Many realtionship?

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

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

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?

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. 

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

Can you share a sample file?

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...

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

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...

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
2.png
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.

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

 

 

 

In the first CCALCULATE, try to store 

Table2'[Date]

in a variable outside CALCULATE  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors