In Power Query, create two tables referencing your sample data:
1: Your filter table, fetching the column headers using
Table.FromList(Table.ColumnNames(#“Sample Data”)),
and then maybe filtering out ID, Name and other categories you don’t need. Rename the column to Attributes.
2: A version of your sample data, where you unpivot all the filter columns: Table.UnpivotOtherColumns(#“Sample Data”, {“ID”, “Name”}, “Attribute”, “Value”)
When loaded to the model, create a relationship between the two tables’ “Attributes” columns
Write the following measure: Count = IF( COUNTROWS(‘Filter’)=1, DISTINCTCOUNT(‘Sample Data Unpivoted’[ID]))
(Edit: Sorry, I was too sloppy… I guess it should have been COUNT(‘Sample Data Unpivoted’[Name]) to replicate your results. But the principle is the same)
You should now hopefully be able to make a table and a slicer:
Hi @Dharma, did the response provided by @tholstrup help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Dharma, we’ve noticed that no response has been received from you since Sep 1.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Dharma, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.
