Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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