Show Category only Contains Multiple Selected Subcategory

[rif_data]

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?

[Anonymous · Accepted solution]

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

[rif_data]

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

[Kedar_Pande]

@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

[rif_data]

Unfortunately this solution not working. 😦