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 this and not found quite the right result.  I have included a pbix file with this post.

 

The Ask

I have a list of datasets in a table, each with a sensitivity rating, and I have a lookup table which provides information on what to do when combining datasets with each other.  I want the user to be able to select, say, 3 of those datasets and show them the result from the look up table for each combination, i.e. Dataset 1 = Low, Dataset 2 = Low therefore the result is Pass, Data 1 = Low, Dataset 4 = High therefore the result is Challenge

 

The Data

These are the two tables which are also in the .pbix file.  In reality our system has thousands of datasets and more combinations and results, but the principle is in this data.

 

https://www.icloud.com/iclouddrive/09aH_-DJs8K8WNvoZyi4CHhQg#Sensitivity 

 

The Result

I would like the user to be able to select several datasets from Table 1, and Power BI output two things as follows

1) A Listing View which is basically a table with the available combinations and the look up included where the datasets intersect

2) A Matrix View which shows the user the result from the lookup where the two values intersect.

 

I would prefer that the same datasets are exluded from the output, so it is not comparing a dataset to itself.

 

Work Done To Date
I have managed to get this to work partly by using a disconnected calculated table of the datasets, but I can only do it if the individual selects 1 dataset from the original and multiple from the calculated.  I then used a measure to select the value of first dataset, and another measure to combine this with the 2nd set of dataset and then a 3rd measure to lookup the results.

However this obviously breaks if more than one dataset is selected in the 1st dataset, and it is also not in the format I want to end up with - it is also a bit clunky for the user.  Also, the slicers are not sycronised across the two disconnected tables, which I know I can resolve using sync slicers, but cant figure out the measures to then compare the values.  Both of these are in the .pbix file

Hope this is a good enough explanation and the .pbix file is suitable.  Looking forward to your response and thanks in advance.

 

Rich

Need Help

6 Replies

  • barritown's avatar
    barritown
    Solution Sage
    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