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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Line Charts and SWITCH Function

Hi All, 

 

I am creating a line chart to show a trend line for company specific metrics. I have used the SWITCH function to construct the formula seem below: 

Formula =
VAR Actual_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Actual1[Actual])
VAR FCST_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Forecasted_Actual1[Actual])
RETURN
SWITCH(
TRUE(),
[Max_Actual1_Date] >= SELECTEDVALUE(s_Date[Date]),        Actual_Metric,
[Max_Actual1_Date] < SELECTEDVALUE(s_Date[Date]),          FCST_Metric,
                                                                                                FCST_Metric
)

The goal here is to use 'SUM(Actual1[Actual])' for the dates that are in our table and for anything outside of that range, use the 'SUM(Forecasted_Actual1[Actual])' data. This formula works on a table at a row level but when using on a clustered line/column chart it does not extend the line at a monthly level to the future dates where the FCST_Metric func. would be evaluated.

For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up. I am trying to understand/fix this issue so that this line is shown at a monthly level and continuous, currently it appears disjoint despite the data itself being continuous. Additionally, the measure is summing the total daily values on my CARD visual whereas it should be the aggregated average. Any fixes on this for my formula? 
 
Additional Context:
s_Date is a M-language created date table that feeds all tables in my schema. The Budget, Actual1 and Forecasted_Actual1 tables are all connected to the s_Date table. The measure [Max_Actual1_Date] returns the max date from the Actual1 table. 

Thank you for your time and patience! 
 
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User


@Anonymous wrote:
For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up.

The SELECTEDVALUE function will return a blank if there are multiple values within the current filter context. If you're grouping at the month level, then you'll get a blank since there isn't a single value to return. You need to tell it what date you want to compare to when multiple are available. One way to do this is to use MAX instead of SELECTEDVALUE.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User


@Anonymous wrote:
For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up.

The SELECTEDVALUE function will return a blank if there are multiple values within the current filter context. If you're grouping at the month level, then you'll get a blank since there isn't a single value to return. You need to tell it what date you want to compare to when multiple are available. One way to do this is to use MAX instead of SELECTEDVALUE.

Anonymous
Not applicable

Hi Alexis, this fixed both of my problems, thank you for the tip and insight. I will remember this for the future! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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