topic Re: Show Category only Contains Multiple Selected Subcategory in DAX Commands and Tips

Show Category only Contains Multiple Selected Subcategory

rif_data — Mon, 23 Dec 2024 11:01:29 GMT

I have a Data Set like this in PowerBI-

Category	Total Value	Sub_Category
AB	100	A
AB	100	B
BC	140	B
BC	140	C
CX	90	C
CX	90	X
AX	40	A
AX	40	A
ABX	200	A
ABX	200	B
ABX	200	X

I want to use a slicer like this- if I multi select A and B in Slicer It will show only the category which has only A and B present (Category AB,ABX from my example). How can I do it?

Re: Show Category only Contains Multiple Selected Subcategory

Kedar_Pande — Mon, 23 Dec 2024 11:06:43 GMT

rif_data

Create a New Measure:

ShowCategory =
VAR SelectedSubCategories = VALUES('Table'[Sub_Category])
VAR CategorySubCategories =
CALCULATETABLE(
VALUES('Table'[Sub_Category]),
ALLEXCEPT('Table', 'Table'[Category])
)
VAR MatchCount = COUNTROWS(INTERSECT(SelectedSubCategories, CategorySubCategories))
VAR TotalSelected = COUNTROWS(SelectedSubCategories)
VAR TotalCategory = COUNTROWS(CategorySubCategories)
RETURN
IF(MatchCount = TotalSelected && MatchCount = TotalCategory, 1, 0)

Add this measure to the filter pane for the visual.
Set the filter to show only rows where ShowCategory = 1.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: Show Category only Contains Multiple Selected Subcategory

rif_data — Mon, 23 Dec 2024 11:38:37 GMT

Unfortunately this solution not working. 😦

Re: Show Category only Contains Multiple Selected Subcategory

Anonymous — Tue, 24 Dec 2024 06:03:07 GMT

Hi rif_data ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a sub category dimension table(DO NOT create any relationship with your fact table)

Sub Categories = VALUES('Table'[Sub_Category])

2. Create a measure as below

Flag = VAR _selsubcat = ALLSELECTED ( 'Sub Categories'[Sub_Category] ) VAR _selcount = COUNTROWS ( _selsubcat ) VAR _category = SELECTEDVALUE ( 'Table'[Category] ) VAR _subcategory = SELECTEDVALUE ( 'Table'[Sub_Category] ) VAR _count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Sub_Category] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Category] = _category && 'Table'[Sub_Category] IN _selsubcat ) ) RETURN IF ( _count = _selcount && _subcategory IN _selsubcat, 1, 0 )

3. Create a table visual

4. Apply a visual-level filter with the condition (Flag is 1) on the above able visual

Best Regards

Re: Show Category only Contains Multiple Selected Subcategory

rif_data — Tue, 24 Dec 2024 07:09:00 GMT

This is EXACTLY WHAT I AM LOOKING FOR! 😊
Thanks Mate!