Forum Discussion
Cascading Filters and Columns
- Anonymous3 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
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