Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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 f...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous , 

    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.   

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

 

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.

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous , 

    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.