Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to add selected value reference line to line chart when interaction with slicer is turned off

Hello,

 

I would like to add an x-axis reference line to my line chart. The reference line uses a measure that takes the selected week minus the selected weeks back. The measure returns the desired value in a card but when I select the measure for the reference line it returns the wrong value. This is because I have interaction with the fiscal week slicer turned off for the line chart. If I turn this on then the line chart only graphs the selected week and hides all others, making it useless.

 

ZLV_1-1657748890544.png

 

Is there a work around here?

 

 

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

There is no way to get the value of the slicer when you turn off the interaction. So let the value of line chart has no interaction with the slicer. 

So, please create a new table named "fiscal week(2)" via VALUES('date'[Fiscal week]) and use the 'fiscal week(2)'[Fiscal week] for the slicer. Do not forget to modify the "R_Week Reference Line" to get the values from the new slicer.

 

If you need more help, please share your pbix file without sensitive data and expect output.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks for your reply. My data (and the x-axis) is all weekly so I don't have a date field. 

amitchandak
Super User
Super User

@Anonymous , with hlep from week rank on week/date table

 

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

you can get measure

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

or

behind by selected weeks

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-[Selected week back]))

 

diff of above can be used as line or as constant line using analytics pane

 

diff= [This week] - [Last week]

 

or

diff= calculate([This week] - [Last week], allselected())

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

In case you want trend till a week back

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.