Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Is there a way to disable the line chart's forecast option based on a condition (e.g. no slicer item is selected)?
My Objective:
- Create a line chart with a 3-period forecast for a selected "Category"
Constraints:
- Categories are selected by a separate slicer.
- When no item in the slicer is selected, the rest of the dashboard elements show other information at one level above "Category" (say "Market").
- The value used in the line chart cannot be aggregated at the "Market" level and is purely a "Category"-specific value.
What I've Done:
- I created a dynamic measure for the value that returns blanks if no item is selected on the slicer. This way, the line chart only appears when a slicer item is selected. This works pretty well.
Measure = IF(SELECTEDVALUE('Table'[Category],0)=0,"",SUM('Table'[Value]))
See sample pbix file here: https://drive.google.com/file/d/19rFV0KRQr2EVbVuIlo99I9a4hRRBCMjd/view?usp=sharing
Where I'm Stuck:
- When I added a forecast to the visual, the line chart no longer disappears when I do not select a slicer item. Instead, it keeps the previous line chart from my last selected item and puts a red X on the upper left, which says, "We need more data."
- This behavior is weird to me, because some items have no data points at all, but the line chart still returns a blank chart as intended.
Has anyone done this before?
Thanks,
A
Solved! Go to Solution.
@Anonymous
Hi,
try this and see if it works:
Measure = IF(ISFILTERED('Table'[Category]), SUM('Table'[Value]), BLANK())
Regards,
Paul
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Hi,
try this and see if it works:
Measure = IF(ISFILTERED('Table'[Category]), SUM('Table'[Value]), BLANK())
Regards,
Paul
Proud to be a Super User!
Paul on Linkedin.
Hi Paul! This works great!
Could you explain why this works while my previous formula doesn't? Is it to using BLANK() over ""? How can I differentiate the two?
Thanks,
A
@Anonymous
My guess is that when you add the forecast line, it is generating a conflict in the expression included SELECTEDVALUE, which is intended for single values. The expression ISFILTERED returns TRUE whenever the column is filtered, whether it be with one OR more "selections" in the "Category" slicer.
Check out the FILTER functions in DAX.
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |