Forum Discussion

Mahonia's avatar
Mahonia
New Member
8 years ago
Solved

Filter by different column depending on user selection

I have a table with several Boolean columns and I need a user selector (either a slicer or a filter) to vary which of these columns is used to filter the visuals.

The data looks like this:

 Dimension, OptionA, OptionB, OptionC

Mem1, TRUE, FALSE, FALSE

Mem2, TRUE, FALSE, TRUE

Mem3, FALSE, FALSE, FALSE

 

I need a user selector that would look like this:

Select Option

OptionA

OptionB

OptionC

 

Then, when the user selects OptionA, the visualisation should be based only on those members which are TRUE in column OptionA. 

 

This would be very easy to do in Tableau using parameters, calculated fields and filters, but I have not found a way to do this in Power BI. If I use a parameter for the selector, this is not available to users in Power BI Service, so this is not useful. 

 

It looks like I will need to add the selector list as a new table, and then build logic using ISFILTERED( ). If I put that logic in a calculated Column it doesn't work as the column doesn't update when the filter is changed; but if I put that logic in a calculated Measure then it can't be used a filter on the visualisations. 

 

Does anyone have any ideas for how this logic could be built?

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Mahonia,

     

    You can refer to below steps to achieve your requirement.

     

    1. Add a selector table as the source of slicer.

    Selector = DATATABLE("Select",string,{{"OptionA"},{"OptionB"},{"OptionC"}})

     

    2. Add a measure to get the value of selected option.

    Tag = 
IF (
    SWITCH (
        SELECTEDVALUE ( Selector[Select] ),
        "OptionA", LASTNONBLANK ( 'SampleData'[OptionA], [OptionA] ),
        "OptionB", LASTNONBLANK ( 'SampleData'[OptionB], [OptionB] ),
        "OptionC", LASTNONBLANK ( 'SampleData'[OptionC], [OptionC] )
    ),
    "Y",
    "N"
)

    3. Add visual level filter on above measure to filter tag "Y" records.

     

    Result:

     

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Mahonia,

     

    You can refer to below steps to achieve your requirement.

     

    1. Add a selector table as the source of slicer.

    Selector = DATATABLE("Select",string,{{"OptionA"},{"OptionB"},{"OptionC"}})

     

    2. Add a measure to get the value of selected option.

    Tag = 
IF (
    SWITCH (
        SELECTEDVALUE ( Selector[Select] ),
        "OptionA", LASTNONBLANK ( 'SampleData'[OptionA], [OptionA] ),
        "OptionB", LASTNONBLANK ( 'SampleData'[OptionB], [OptionB] ),
        "OptionC", LASTNONBLANK ( 'SampleData'[OptionC], [OptionC] )
    ),
    "Y",
    "N"
)

    3. Add visual level filter on above measure to filter tag "Y" records.

     

    Result:

     

    Regards,

    Xiaoxin Sheng