Filter data that match all conditions in other table (multiple columns) - row by row

[Marek12345]

Hi,

hope you can help me with the following issue.

I have one table with users and conditions which the users have:

Users	Conditions
User 1	C 1
User 1	C 2
User 1	C 3
User 1	C 4
User 1	C 5
User 1	C 6
User 2	C 1
User 2	C 2
User 2	C 5

And a table with condition sets which create a certain criteria I want to look for:

Criteria	Column 1	Column 2	Column 3	Column 4
Criteria 1	C 1	C 2	C 5	C 6
Criteria 2	C 1	C 2	C 3	C 5
Criteria 3	C 1	C 2	C 7
Criteria 4	C 1	C 3	C 6	C 8

What I need is to either to (don't know which option is better):

1. transpose these table, so I load only relevant rows to the model:

- User 1 meets Criteria 1 and Criteria 2 so C1, C2, C5, C6 and C3 should be uploaded to the model, but not C4 (which he also has).

- User 2 has criteria 1 met

- User 3 has no criteria met
- User 4 has criteria 4 met (C1, C3, C6, C8, but not C2)

2. or upload the data in the model and be able to indidate which user has which criteria met, ex. User 1 should be mentioned twice as he has both criteria 1 and 2 met.

I uploaded the pbix and excel file here Sample  

Thanks for help!

[v-jingzhan-msft · Accepted solution]

Hi @Marek12345 

Do you mind creating a table visual like this?

Or a matrix visual like this?

To achieve above result, you need to transform the Criteria table into below format. Steps are:

1. Select "Criteria" column and unpivot other columns. 

2. Filter out empty values in "Value" column. 

Then create the following measure

Is Met ? = 
VAR _criteriaFields = SUMMARIZE(Criteria,Criteria[Value])
VAR _userFields = SUMMARIZE('Table','Table'[Conditions])
RETURN
IF(COUNTROWS(INTERSECT(_criteriaFields,_userFields))=COUNTROWS(_criteriaFields),1,0)

As two tables are not connected to each other, you need to add this measure to the table or matrix, otherwise it will display an error saying "Can't determine relationships between fields."

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Marek12345]

Hi,

thank you for support. Really appreciate!

The solution worked and I have built the whole model based on it. 

The issue I have now is this solution will not be sustainable as the measure takes too much time to recalculate for the whole model. I either need to filter by User / user group or by certain criteria. The measure recalculates each time I filter the visual / drilldown.

I would like to move the calculation to power query, so finally I get a table which shows yes / no for each user and criteria.

Can you help me with that?

Thanks!

[lbendlin]

This will stumble at the first hurdle - 

User 1 meets Criteria 1 and Criteria 2 so C1, C2, C5, C6 and C3 should be uploaded to the model, but not C4 (which he also has).

"User"  plays no role in the loading of data into the model.

You need to load all data, and then use filters or RLS or perspectives to provide each user with their view.

[Marek12345]

Ok, I will load all the data into the model - no problem.

I don't want to have a view per user, I woul like to get one table that is stating the following:

User 1	Criteria 1
User 1	Criteria 2
User 2	Criteria 1
User 4	Criteria 4

Can you help me with such a measure?