Key value pair in slicer

[jaiye]

Hello Power BI Community,
I'm working on a Power BI report and need some assistance with setting up a hierarchical slicer and ensuring dynamic filtering in my matrix visualization. Here’s the scenario I’m dealing with:

Data Structure:
• I have a table with Key and Value columns.
• Key can be things like "Assisting Adjuster," and Value can be various text or numeric data associated with these keys.
Requirements:
1 Hierarchical Slicer: I need a slicer that allows users to select both keys and values. When a key is expanded, it should show the related values.
2 Dynamic Filtering: When users select a value in the slicer, the matrix should display filtered data corresponding to that value while keeping other columns visible.

Steps Taken So Far:
• I created a combined table in Power Query to differentiate between keys and values.
• I added a slicer using this combined table.
• I attempted to create DAX measures to handle the filtering but encountered errors when dealing with multiple selections.
Challenges:
• The measure I created throws an error about multiple columns not being convertible to a scalar value.
• I need guidance on correctly implementing dynamic filtering so that when a value is selected, the matrix only shows the relevant data but retains all columns.
What I Need Help With:
1 Creating a hierarchical slicer that supports selecting multiple keys and values.
2 Developing a measure or a method to filter the matrix visualization dynamically based on the slicer selection while keeping other columns visible.
Any guidance, examples, or resources would be greatly appreciated!
Thank you!

@amitchandak; 

[Sahir_Maharaj]

Hello @jaiye,

To perform dynamic filtering, you can use DAX:

SelectedValueMeasure = 
VAR SelectedKey = SELECTEDVALUE(YourTable[Key])
VAR SelectedValue = SELECTEDVALUE(YourTable[Value])
RETURN
    IF(
        ISBLANK(SelectedKey),
        1,
        IF(
            ISBLANK(SelectedValue),
            IF(MAX(YourData[Key]) = SelectedKey, 1, 0),
            IF(MAX(YourData[Key]) = SelectedKey && MAX(YourData[Value]) = SelectedValue, 1, 0)
        )
    )

Hope this helps - Alternatively, lets connect and I can guide you through the process - Yes, its FREE 🙂

 (https://topmate.io/sahirmaharaj/649292)

[jaiye]

Hi @Sahir_Maharaj 

Unfortunately, this is not working. When i select the keys it is returning 1 for every keys that is not selected in my slicer