Forum Discussion

baconandpizza's avatar
baconandpizza
New Member
2 years ago
Solved

Cascading Filters and Columns

Hi,

 

Could you help me set up my data in a way where the slicers are cascading along with the visual.

Here is a sample data:

 

And this is how I want my visualization to look like:

 

To explain further, I if Ticket 1 is filtered (for example, it is filtered to A), Ticket 2 will be in the visualization (so, it should be AA and BB in the chart). But, how can I do this without dragging each column in the visual field when I change the filters.

 

Since, I want my chart to show Ticket 3 in the X-axis when Ticket 2 is filtered. I have to drag Ticket 3 column in the visual to do this. Is there a way to do this?

 

Thank you so much!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi baconandpizza ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Select the columns "Ticket 1", "Ticket 2","Ticket 3","Ticket 4" and "Ticket 5" and unpivot these columns

    = Table.Unpivot(#"Changed Type", {"Ticket 1", "Ticket 2", "Ticket 3", "Ticket 4", "Ticket 5"}, "Ticket Type", "Value")

    2. Create a dimension table

    Tickets = GROUPBY('Table','Table'[Ticket Type],'Table'[Value])

    3. Create a measure as below to get the count of customer id which fulfill the filters

    Count of customer = 
VAR _selticket =
    SELECTEDVALUE ( 'Tickets'[Ticket Type] )
VAR _seltvalue =
    ALLSELECTED ( 'Tickets'[Value] )
VAR _nticket =
    (
        LEFT ( _selticket, LEN ( _selticket ) - 1 )
            & ( VALUE ( RIGHT ( _selticket, 1 ) ) + 1 )
    )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ticket Type] = _selticket
                && 'Table'[Value] IN _seltvalue
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ticket Type] = _nticket
                && 'Table'[Customer ID] IN _tab
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Customer ID] ),
        FILTER ( 'Table', 'Table'[Value] IN _tab2 )
    )

    4. Create a column chart

    By the way, if select "Ticket 5", what's the data should display in the visual?

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi baconandpizza ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Select the columns "Ticket 1", "Ticket 2","Ticket 3","Ticket 4" and "Ticket 5" and unpivot these columns

    = Table.Unpivot(#"Changed Type", {"Ticket 1", "Ticket 2", "Ticket 3", "Ticket 4", "Ticket 5"}, "Ticket Type", "Value")

    2. Create a dimension table

    Tickets = GROUPBY('Table','Table'[Ticket Type],'Table'[Value])

    3. Create a measure as below to get the count of customer id which fulfill the filters

    Count of customer = 
VAR _selticket =
    SELECTEDVALUE ( 'Tickets'[Ticket Type] )
VAR _seltvalue =
    ALLSELECTED ( 'Tickets'[Value] )
VAR _nticket =
    (
        LEFT ( _selticket, LEN ( _selticket ) - 1 )
            & ( VALUE ( RIGHT ( _selticket, 1 ) ) + 1 )
    )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ticket Type] = _selticket
                && 'Table'[Value] IN _seltvalue
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ticket Type] = _nticket
                && 'Table'[Customer ID] IN _tab
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Customer ID] ),
        FILTER ( 'Table', 'Table'[Value] IN _tab2 )
    )

    4. Create a column chart

    By the way, if select "Ticket 5", what's the data should display in the visual?

    Best Regards