Filtering the items displayed in a slicer based on selected value

[ValeriaBreve]

Hello,

I am trying to reduce the number of options in a slicer based on the values selected by the user on a table.

So if the user clicks on the row of the table, the slicer should update to only reflect values that are pertinent.

The slicer is a column from a Dimension Table D_DescEvents[EventCode]

The table is coming from a fact table, which has F_StepsCTData_V[EventCode] as the relevant column that will be selected 

D_DescEvents[EventCode] 1 --> n F_StepsCTData_V[EventCode]

So I have created a measure 

# Selected BatchId =

SELECTEDVALUE(F_StepsCTData_V[IdBatch])

which is telling me whether a row from the table is selected, and this works well.

I cannot understand how to get the slicer to only show the relevant options filtered down for that row. (multiple EventCodes for 1 IdBatch)

I have tried a number of things.... for ex:

VAR _Table1=

SELECTCOLUMNS(

    SUMMARIZECOLUMNS(

F_StepsCTData_V[EventCode],

FILTER(

    F_StepsCTData_V,

    F_StepsCTData_V[IdBatch] =

    [# Selected BatchId]

)

),

"Event Code",[EventCode])

VAR _TableEvents=

SELECTCOLUMNS(

D_DescEvents,

"Event Code",D_DescEvents[EventCode]

)

VAR IntersectResults=

INTERSECT(_Table1,_TableEvents)

RETURN

IntersectResults

This is giving me a table with only the relevant EventCodes that I need, but I don't seem to be able to find a way to apply it to the slicer...

Thanks a lot for your help - I really need a pair of fresh eyes on this.

Kind regards

Valeria

[v-vpabbu]

Hi @ValeriaBreve,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @ValeriaBreve,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[ValeriaBreve]

Hi @v-vpabbu , thanks for the follow up. So what I am trying to accomplish is not possible in PowerBI as is today - that is to say, a drop down slicer that accepts indirect filtering.

Point well taken....

Just when you say "behavior is known" - I have not seen this limitation in any of the official MSFT documentation, maybe I missed it? If yes, could you please point me to where it is?

Thanks!

[v-vpabbu]

Hi @ValeriaBreve,

Here is the official microsoft document link with limitation

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-slicer-numeric-range 

Similar to the numeric range slicer limitation, categorical slicers also cannot be filtered by measures. While this is not explicitly documented, it is a known behavior confirmed through practical experience and community discussions.

Regards,

Vinay Pabbu

[ValeriaBreve]

Hi @v-vpabbu , in reality slicers can be filtered by measures.

What you highlight in the text is about a numeric range slicer that does not accept a measure as a field, so not my case of a text slicer using a measure as a filter - hence my telling you that I don't see the limitation written in the docs.

Reality is that old slicers don't accept INDIRECT measures as filters. What I mean is that, in my case, issue is not with the main measure, it's with this part of my measure:    

[# Selected BatchId]

which is

# Selected BatchId = SELECTEDVALUE(F_StepsCTData_V[IdBatch])

so returning  in my case an integer of what Id is selected on page by the user.

If, insteaf of this secondary measure, you use an available Id as a static value, the slicer does work correctly...

[v-vpabbu · Accepted solution]

Hi @ValeriaBreve,

slicers can be filtered by measures when the measure evaluates correctly within the slicer’s context. The limitation in your scenario arises because the slicer does not receive filter context from user selections made in other visuals.

This behavior is due to slicers not responding to indirect or cross-visual context.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @ValeriaBreve,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[ValeriaBreve]

@Greg_Deckler , the interactions are already on, however here users are highlighting values - not properly filtering - and from the fact table, which has then to propagate to the opposite direction (vs. the relationship)  to the dimension table in the slicer.
Hence my thought of using a SELECTEDVALUE based measure to define which record from the fact table is highlighted, and then somehow use that to filter down the dimension table through a measure used as a filter in the slicer (I hope this is not too confusing).

But I am getting stuck in writing that measure... I usually get away with a countrows(fact table)>0 put as filter of the slicers, but in this case the fact that data is not filtered, but highlighted only, makes things slightly more complex.

Thanks a lot for your help!

[Greg_Deckler]

@ValeriaBreve Other visuals do not normally affect slicers so you need to use Edit Interactions to turn those interactions on. That might be the issue.

[ValeriaBreve]

@Greg_Deckler , as a last thought, I am not sure what is going on, but if I use the below measure as a filter  in a table, or button slicer, or list slicer, it all works correctly and gives me the expected result (filtering down the entries to what corresponds to the highlighted IdBatch value).

BUT it does not work with a simple slicer. 

It really is funny, as when I transform the slicer into another visual object, it immediately reduces the entries as per measure filter.

I have tried to re-build the slicer from scratch, and still same behaviour.

Is this something known I have missed? 

Thanks!

VAR _Table1=

SELECTCOLUMNS(

    SUMMARIZECOLUMNS(

F_StepsCTData_V[EventCode],

FILTER(

    F_StepsCTData_V,

    F_StepsCTData_V[IdBatch] =

    [# Selected BatchId]

)

),

"Event Code",[EventCode])

VAR _TableEvents=

SELECTCOLUMNS(

D_DescEvents,

"Event Code",D_DescEvents[EventCode]

)

VAR IntersectResults=

INTERSECT(_Table1,_TableEvents)

RETURN

COUNTROWS(IntersectResults)

[v-vpabbu]

Hi @ValeriaBreve,

This behavior is known and often happens due to the way slicers interact with DAX measures. Unlike other visuals, a simple slicer requires a direct relationship with a column in your data model.
When you use a measure as a filter, it works in tables, buttons, and other slicers that allow indirect filtering, but a standard slicer only works with actual column values.

If possible, create a calculated column instead of a measure and use that in the slicer.
Ensure the slicer is set to "Single select" or "Multi-select" properly, and verify the relationships between tables.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu