Forum Discussion
Filter data that match all conditions in other table (multiple columns) - row by row
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!
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!
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!