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
We have data coming in at England level as well as more granular geographies. We would like to have the England line in our line chart visible regardless of the filters applied to the chart, so that whatever is selected can be compared to the England lines. Is this possible?
Solved! Go to Solution.
hi, @ABNHS84
Based on my research, you could use this way:
Step1:
Use this formula to add a dim group table.
Dim Group = UNION(VALUES('Table'[Group]),ROW("Group","persistent"))
Step2:
Create the relationship with basic data table.
Step3:
Create a measure by this formula:
Measure = IF ( SELECTEDVALUE ( 'Dim Group'[Group] ) = "persistent", CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Group] = "England" ) ), CALCULATE ( SUM ( 'Table'[Value] ) ) )
Step4:
Now in the time series line chart, drag Group field from Dim Group table.
and use Group filed from basic data table as a slicer.
Result:
However, whatever the slicers are set to (although England is not selected), the line for England to remain visible (persistend line).
and here is pbix file, please try it.
Best Regards,
Lin
Hi @ABNHS84
The below should do the trick.
england by measure = CALCULATE( [measure], redion[country] = "England" )
Hope this helps.
Mariusz
Thanks for the quick response Mariusz,
I don't think this solves by problem though.
I have data in the below format and want to produce a time series line chart. However, whatever the slicers are set to, I want to the line for England to remain visible.
+---------+-------+-----+------+------+ | Group | April | May | June | July | +---------+-------+-----+------+------+ | England | 20% | 25% | 30% | 50% | +---------+-------+-----+------+------+ | Locale | 10% | 12% | 15% | 30% | +---------+-------+-----+------+------+ | locale2 | 20% | 40% | 60% | 70% | +---------+-------+-----+------+------+
Hi,
In the Query Editor, right click on the first column heading and Unpivot other columns. Create a slicer for Group and ensure that England is always selected in that slicer (along with any other Group). Now build your line chart visual.
hi, @ABNHS84
Based on my research, you could use this way:
Step1:
Use this formula to add a dim group table.
Dim Group = UNION(VALUES('Table'[Group]),ROW("Group","persistent"))
Step2:
Create the relationship with basic data table.
Step3:
Create a measure by this formula:
Measure = IF ( SELECTEDVALUE ( 'Dim Group'[Group] ) = "persistent", CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Group] = "England" ) ), CALCULATE ( SUM ( 'Table'[Value] ) ) )
Step4:
Now in the time series line chart, drag Group field from Dim Group table.
and use Group filed from basic data table as a slicer.
Result:
However, whatever the slicers are set to (although England is not selected), the line for England to remain visible (persistend line).
and here is pbix file, please try it.
Best Regards,
Lin
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 |
---|---|
93 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
141 | |
114 | |
111 | |
59 | |
59 |