Compare Multiple Rows to Each Other and Lookup

[Dilbertfan]

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

[barritown · Accepted solution]

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

[barritown · Accepted solution]

The list solution can be achieved with the help of the same measure and specific settings in a matrix.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Dilbertfan]

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]

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]

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

[barritown]

My pleasure. 🙂

Yup, in case you use it in a card, the solution will work only your slicers allows only one value to be selected.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

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