Forum Discussion

Dilbertfan's avatar
Dilbertfan
Frequent Visitor
1 year ago
Solved

Compare Multiple Rows to Each Other and Lookup

Hi All.  First time on posting here, so hope I get the right information.  Since starting Power BI 6 months ago, this forum has been a fantastic resource, so thank you, but have searched loads for th...
Need Help
  • barritown's avatar
    barritown
    1 year ago

    Hi Dilbertfan,

    The matrix solution is pretty easy to achieve.

     

    You will need two non-related Dataset tables, two non-synched slicers and a measure like the one below.

    _lookupMeasure = 
VAR ds1 = SELECTEDVALUE(Datasets[Name])
VAR ds2 = SELECTEDVALUE('Datasets 2'[Name])
VAR s1 = SELECTEDVALUE(Datasets[Sensitivity])
VAR s2 = SELECTEDVALUE('Datasets 2'[Sensitivity])
RETURN IF ( ds1 <> ds2,
    MAXX ( FILTER ( Lookup, Lookup[Dataset 1] = s1 && Lookup[Dataset 2] = s2 ), [Result] ) )

     

    Please check the Musings tab of your file modified by me.

     

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

Dilbertfan's avatar
Dilbertfan
Frequent Visitor
1 year ago

Hi Alexander.

 

Thanks so much for taking the time to respond.  Both solutions are great and exactly what I need - didnt realise it was that "simple" :-).

 

Strangely enough, I had also solved it in another way last night using CROSSJOIN between the two disconneted tables to create a calculated table with a combination of all entries, and then using calculated column lookup, but this solution is far simpler and avoids having a calculated table in it with ~4m rows :-)!!

 

Just a quick question for my knowledge barritown - you have used SELECTEDVALUE, but have selected multiple values in the table.  I thought SELECTEDVALUE was only useful when one thing was selected?  How is it passing multiple values into the Lookup table to filter it?

 

I get that the measure is acting on the row context in the lookup table, but how does it know which one of the values selected to apply to that row?


Thanks for this

 

Rich

barritown's avatar
barritown
Solution Sage
1 year ago

Hi Rich,

In each matrix cell there will be only one value for this function. If you'll imagine putting SELECTEDVALUE in a matrix cell, you will see that it can get only one value from Table1 and Table2.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

  • Dilbertfan's avatar
    Dilbertfan
    Frequent Visitor
    1 year ago

    Thanks barritown - great of you to explain.

     

    Makes sense, just initially hard to get your head around - I sort of knew this, but it is the act of selecting 4 records in the slicer, which is throwing me off.  So in the context of the table or matrix it works, but if you tried to use SELECTEDVALUE in a card, then it would break as you dont have the filter context to bring it to a single value?

     

    Thanks again

     

    Rich