Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 |