Select / Display Table Rows with Hierarchy Slicer with Multi-Selection Criteria Met

[LijunChen]

Dear Community, 

I have a staff qualifications data table, which include following columns amoung others: Staff ID, (Qualifcattions) Category_1, Subcategory_1, and Years ( of training / experience for each). As shown in the screenshot below, In the table, Each Staff ID has a Years value (0, 2, 3-5, 6-9, 9 plus) for each of the subcagory_1 value ( such as ing, ems, QA, etc, nested in each category_1), which is a subcateogry of category_1 (pro, sys, QA, ems, etc.). 

I created a hierarchy slicer with Cateogry_1, Subcategory_1 and Years. We can make multiple selections for cateogry, and subcategory and their number of years in each. We may also select multiple Years value for each subcagegory (6-9 years, and 9 plus). The table visual (matrix) on the right will display the Staff ID's who were filtered based on the slicer selections. 

As shown in the table visual, with multiple selections in the slicer, the table shows that each ID can have one to mutliple rows since some do not meet all the selecte critiria. I would like to create a measure or use some other approach to keep only those ID's in the table with the most number of rows (that is they meet the most criteria since each row reprsent one criteria met). As shown in the screenshot, the slicer selected two category value, 2 subcategory values, and 3 Years values ( with 2 for Subcategory_1 'ems'). The tables shows that ID 2 and ID 4 both have 2 rows since they meet the two subcategory creteria ('ing' and 'ems'). But ID 5, 6, and 7 have only one row as they meet only 1 criteria. I would like to keep only ID 2 and 4 and exclude 5,6,7 and the rest from the table. 

I don't know how I can achieve this? How to filter the table rows to keep the ID's that have the same numbers of rows as the number of subcatetory selected, or filter the table to keep the ID's with most number of rows (maybe lower than the criteria selected)? 

Any help / advice will be appreciated. 

Thanks. 

[v-nuoc-msft]

Hi @LijunChen 

For your question, here is the method I provided:

Here's some dummy data

If you want to query two or more records through the slicer, you can create a measure to count the number of staff id queries.

category_records = 
CALCULATE(COUNT('Table'[Category_1]), 
FILTER(ALL('Table'), 'Table'[Staff ID] = MAX('Table'[Staff ID])))

Select the visual, put the measure into the Filters and apply the filter you want, here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LijunChen · Accepted solution]

Hi @v-nuoc-msft , 

Thanks for your response. I tried your Dax formula, but it gives me all records of categories a person have in the whole data table instead of the just selected.  

So I revised your formulae to look like this:

category_records = 
CALCULATE(COUNT('Table'[Subcategory_1]), 
FILTER(ALLSELECTED('Table'), 'Table'[Staff ID] = MAX('Table'[Staff ID])))

I changed column "category_1" to "subcategory_1" since it is the number of sub category I am looking at. I also changed the "All('Table'" to "ALLSELECTED('Table') since I am counting the number of subcategory records for each of the Staff IDs included in the Table visual. 

Also Instead of using the Measure directly in the filter, I used the measure as a filter under Staff ID: to filter Staff ID by the Top N = 1 measure values. Thus the table will keep the names that meet most number of subcategories selected.