Forum Discussion
Measure value in SWITCH function returning as NaN
I am building a SWITCH function to use within a line graph but having difficulties returning the expected values.
In the function I am using a CALCULATE function on the measures to return the expected VALUES but returing an NaN error. In a different table however, the same measures that are being used total to the expected outcome that is desired.
How would I modify the query below in order to return either the decimal number or percentages that are listed in the screenshot?
Hi htbull_ ,
The NaN values are occurring because MAX('calendar'[Date].[Year]) inside your SWITCH is not always reliable in visuals like line graphs, where each row already represents a year. This causes context mismatches, leading to blank evaluations.
To resolve this, we suggest replacing MAX(...) with SELECTEDVALUE(...) and removing the unnecessary CALCULATE wrappers:
GL Annual Rate Change - Line Graph = SWITCH( SELECTEDVALUE('calendar'[Date].[Year]), 2019, [GL Annual Rate Change - 2019], 2020, [GL Annual Rate Change - 2020], 2021, [GL Annual Rate Change - 2021], 2022, [GL Annual Rate Change - 2022], 2023, [GL Annual Rate Change - 2023], 2024, [GL Annual Rate Change - 2024], BLANK() )This respects the row context from your line chart and allows each measure to evaluate as intended. Also, ensure each measure handles division using DIVIDE() with a default value to prevent NaN.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
7 Replies
Hi htbull_ ,
Thank you for reaching out to the Microsoft fabric community forum.
The problem you’re seeing with NaN happens because, inside your measure, some calculations (like divisions) might be causing errors or returning blank values in the current filter context.
When the CALCULATE inside your SWITCH can’t find data for a year or ends up dividing by zero or blank, the result becomes NaN.
Instead of writing a SWITCH with every year hard-coded, try this simpler measure that works dynamically with the year your line graph uses:
GL Annual Rate Change - Line Graph = VAR SelectedYear = MAX('Calendar'[Date].[Year]) RETURN CALCULATE( [GL Annual Rate Change], -- your base measure for rate change 'Calendar'[Date].[Year] = SelectedYear )Using the above you get the annual rate change measure but only for the selected year in the graph.
- Make sure your base measure [GL Annual Rate Change] handles blanks or zero divisions safely. Use the DIVIDE() function or check for blank values to avoid NaN.
- If you want to keep using SWITCH, add checks like IF(ISBLANK(...), 0, ...) for each year’s measure to prevent NaN.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
Hey Anonymous , both measures listed for the 2021 rate change return the expected numerical value that I am looking for. Shouldn't this logic also be applied within the SWITCH function?
Or am I missing a convert component?As for the simpler query, I am unable to capture capture all rates values in one measure because each year has it's own unique calculation. Are there any possible work arounds for this?
Hi htbull_ ,
The NaN values are occurring because MAX('calendar'[Date].[Year]) inside your SWITCH is not always reliable in visuals like line graphs, where each row already represents a year. This causes context mismatches, leading to blank evaluations.
To resolve this, we suggest replacing MAX(...) with SELECTEDVALUE(...) and removing the unnecessary CALCULATE wrappers:
GL Annual Rate Change - Line Graph = SWITCH( SELECTEDVALUE('calendar'[Date].[Year]), 2019, [GL Annual Rate Change - 2019], 2020, [GL Annual Rate Change - 2020], 2021, [GL Annual Rate Change - 2021], 2022, [GL Annual Rate Change - 2022], 2023, [GL Annual Rate Change - 2023], 2024, [GL Annual Rate Change - 2024], BLANK() )This respects the row context from your line chart and allows each measure to evaluate as intended. Also, ensure each measure handles division using DIVIDE() with a default value to prevent NaN.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi htbull_ ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
