Forum Discussion
Show Category only Contains Multiple Selected Subcategory
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?
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
4 Replies
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
This is EXACTLY WHAT I AM LOOKING FOR! 😊
Thanks Mate!
- Kedar_Pande
Super User
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 LinkedInUnfortunately this solution not working. 😦
