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

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