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
MarkCBB
Helper V
Helper V

Chart data contains not a number (NaN) values

Hi There,

 

I have a Fact table containing sales per date (there are some dates missing), I have then added a calendar table that has dates from 1900-01-01 to 2050-12-31. 

 

I have created a column chart with the calendar dates hierarchy (Year,Q,M,D) and added this to the axis, and use the following measure to display the values:

Total Value = CALCULATE(SUM(SALES[VALUE]))

 

When on the Year level all it good, however as soon as I drop to the next level i.e. Quarter, I get the (I) appear that contains the following warning "Chart data contains not a number (NaN) values".

 

I used the following Dax measure to check for errors in the VALUE column:

CheckForErrors = ISERROR(SUM(SALES[VALUE])) - This returned FALSE.

 

I have checked the Value column for zeros, Blanks/Null - there are none. 

 

I have toggled the X-Axis from Type from Categorical to continuous. and I still get the (I) message appear.

 

I have added the data to a matrix vis and do not see any problems. 

 

If I change the Date hierarchy  to just dates, I dont get the errror. 

 

I have tried to create a simplier version of the pbi file to share, but when I do, I dont get the error. 

 

What is going on? what could I be overlooking?

 

I am happy for someone to T-V into my PC to check it out if that helps in anyway

 

 

 

 

15 REPLIES 15
kcantor
Community Champion
Community Champion

I would suggest that you reduce the scope of your date table. In the past, some older dates have played Mary Hobb with my charts and tables. I generally keep my date table as small as possible as it can be quite the burden.

Other than that, we will need to wait on other more learn-ed folks to chime in.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@kcantor Just gave that a go, but alas no change, I still get the message, I have also added remove Errors to the query, but no luck 😞

That message isn't always a true error. Is the visual itself displaying incorrect results, other than that message? I created a text measure to be used in a tooltip and it set off the NAN warning. It didn't hurt anything because it was N supposed to be A N. Are you using any measures on the visual that return text, or perhaps that technically don't return text values but the measure is accidentally set to a text data type?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman I agree - I think I've seen this when trying to plot too many values on a chart

 

I think there's a limit on how many values you can place on the X-axis (150 years * 4 Quarters) ???

 

 

KHorseman
Community Champion
Community Champion

@Sean Great Scott, man! My largest date table is only 8 years! What database were you working in that went back 150 years?!

 

I think the "Too many values" thing is a different error. It does show up with the same indicator, the little "i" in a circle, but I'm not sure that's related other than being an error message. Not even an error message. It's really just a warning indicator. It's probably important to distinguish the little "i" messages from actual error messages like what you get when your visual won't even display at all because APPARENTLY THIS MEASURE HAS CIRCULAR DEPENDENCIES THAT INVOLVE COLUMNS THAT DON'T EVEN EXIST ANYMORE EVEN THOUGH I AM NOT REFERENCING THOSE COLUMNS ANYWHERE YOU STUPID I'm sorry.. I let it get personal. Breathe... Breathe... I'm fine. It's fine, I'm just having a really hard time right now... *bursts into tears*





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Yep it was an "i" in a circle. Right again! - You are the OneWithAnswer after all Smiley Very Happy Smiley LOL

 

Getting back to the issue here...  I don't have that much data either!

But I'm assuming @MarkCBB does because he created a calendar table from 1900-01-01 to 2050-12-31.

Why else would he do this - he must have the data to match???

 

 

 

 

 

 

KHorseman
Community Champion
Community Champion

@Sean oh I didn't even register the dates he mentioned. Yeah. I think DAX only supports dates starting March 1, 1900. Anything earlier than that returns an error. So @MarkCBB you probably have two months worth of invalid dates.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Yes of course Lotus 1-2-3 - the original culprit!

 

"the first officially supported date by DAX is March 1, 1900"

KHorseman
Community Champion
Community Champion

That is the sixth-stupidest data type specification I've ever seen.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@MarkCBB what's the next date after February 28, 1900 in your Calendar?

Firstly, you guys are awesome, I don’t often wake up at 3am, check my emails and look forward to reading them. I am having so much fun learning PBI (Pulling out my hair – but still fun). Secondly thank you very much for taking the time to help me with this.

 

Ok, let me answer all your questions:

 

I have updated the query to the calendar table to start from the earliest date and end on the latest date of the Sales table, I used the below (Not dynamic, so I will need to figure this out at a later stage – If this solves the problem)

#"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each [Date] >= #date(2014, 1, 1) and [Date] <= #date(2016, 5, 31))

 

(note; I created my Calendar table using excel, and really just picked a huge range – no real reason, I just figured rather have a table with a lot of dates and I could use it for other projects)

(note; my sales table does not have sales for each and every day) – Don’t think that this is the issue.

 

My Calendar table consists of the following columns:

Date – Formatted as Date

Day – formatted as Whole number (This is the day of the month)

Day of Week – Formatted as Text (This is Mon, Tue, Wed, etc…)

Month_ID – Formatted as Whole number (This is 1-12, 1 = Jan, 12 = Dec)

Month – Formatted as Text (This is Jan, Feb, Mar, etc…)

Year – Formatted as Whole Number (This is the year 2014,2015,2016)

Year-Month – formatted as Text (This is a Concatenation of Year and Month i.e. 2016-01, 2016-02 etc…)

 

The Visual itself is displaying the correct values and there are no other Values used besides “Total Value”, Tooltips are empty, no legend, However I do Have the “Total Value” in the Color saturation well. – Interesting observation here, When I dropped from year to Quarter, the color saturations would disappear (To the default color), however when I removed “Total Value” from the color saturation well and then re added it, the color’s now appear on all levels and work as expected.

 

I have also tried he following to see if I can track down the reason to the little I icon:

 

Added a filter to each table to double ensure that there are no null values (Over kill, but at this point worth a try) – no change, still have the little I button

Reduced my Sales table to only the latest 5 months – no change, still have the little I button

Reduced my Sales table to only 1 month (The latest one) – no change, still have the little I button

 

At this point, re-created the chart on a new page in the same pbix file - !!!!!! no little I button!!!!!! It is gone!!! (Maybe the chart stores the info button and doesn’t clear it even after the problem has been corrected, possible bug in PBI?)

Ok, then,

Increased the sales range to the latest 5 months – Still, no little I button – YAY!!!

Increased my Sales table to include the full date range – Still no I button, this is looking good so far

Next, I went back to the page that has the original chart (Remember I re-created the chart on a new page), no little I button, YAY!!!

I then added the last thing, a trend line, problem found, the trend line caused the I button to appear. Not sure why, just happy to know that this is the actual cause.

 

I then removed the query line I added “#"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each [Date] >= #date(2014, 1, 1) and [Date] <= #date(2016, 5, 31))”  still all good, no little I button. Problem solved.

 

I do however have a somewhat related question, when on the year level view of the chart, I get the 2 year, 2015, 2016. Before when I dropped to the next level down i.e. quarter, The X-axis labels used to concatenate the year to the Quarter, and the same with the next level down, however this is no happening anymore, instead I am getting only 4 quarters, so the 2 quarters from 2016 and being added to Q1 and Q2, so Q1 and Q2 now consist of 2 years’ data. I have tried switching between x-axis type categorical and continuous. I know this is a silly challenge in comparison to the above one, but do either of you know how I can get it not to roll the level into each other?

 

@MarkCBB thanks for the detailed info. That will help. I have a new theory, and I've even managed to reproduce what you're describing with it:

 

Trendlines require that the axis be a numeric column, or at least one that can be treated as numeric like a date. If you've set up a drill-down hierarchy that includes something like a text-type column for Month or Quarter, that could cause the trendline to fail, except you're not on that drill level. If you're viewing it at the year or date level, the trendline can still calculate but you'll get the warning because when you drill down to a text level the trendline won't know what to do. In my testing I don't always get the warning at numeric levels, but I do get it when I drill to a text level like quarter. Does that sound like what you're seeing?

 

As for drilling to quarter and getting two year's worth, that's easy. You need a unique quarter per year. Your quarter column probably looks something like this:

 

Quarter

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

 

When you drill down to quarter you're telling the visual, "For each value of Quarter in my date table, calculate my measure." So it does. It gives you the measure for every date corresponding to Q1. That's two Q1s calculated together. This can be useful if, for instance, you want to know which quarter in general has tended to have the best sales, but it is not useful if you want to have a single year's quarterly sales. For that you need a column like this:

 

Quarter of Year

Q1 2015

Q2 2015

Q3 2015

Q4 2015

Q1 2016

Q2 2016

Q3 2016

Q4 2016

 

Same thing, by the way, for Month. My date table has four such columns, both Quarter and Quarter of Year, and both Month and Month of Year. That way I can see both multi-year aggregates for averaging measures and single-year aggregates for totals and such.

 

Another note on date table ranges: my usual practice is to start from January 1 of the earliest year in any data table, and end on December 31 of the third year after the latest in any data table. In my experience it's extremely optimistic to expect that any report will be used unchanged for more than 3 years. Either the report will be replaced with something else, or during some inevitable future revision the date range can be extended as necessary. Your mileage may vary, but there is probably some number of years that will suit this practice to your needs.

 

I have no idea about the color saturation thing. I'll have to do some testing. You say you're using Total Value as color saturation. I gather that's just a basic SUM measure? What are you using as the value measure for the chart? Like what's the measure you're using for column height?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank goodness for this thread. It has saved my sanity. I had the same NaN error message as MarkCBB on many stacked column charts in a large report. Nothing I tried would fix the issue. I could create identical charts to the ones displaying the error, and they had no error! The only solution appeared to be to recreate all the problem charts :-(.

 

So I follow Mark's lead and look for a trendline. But my charts have non-numerical X axes and the Trendline option didn't appear in the Analytics pane. But if I changed the chart to a line chart and changed the X axis to something numerical (I used the Date column from my date table), the Trendline option appeared, a trendline was indeed present, and deleting it, then reversing the X-axis and chart type changes fixed the issue.

 

Apparently, the person who initially created the charts (not me) added trendlines, but then changed the chart type and X-axis. So the trendline was hiding there all the time causing the NaN warning, even though it was not accessible in the Analytics pane. Very tricky.

Oh my gosh! You are my hero. This is exactly my issue. Bloody disappearing Trendline option. Ha.

Cheers!


@SteelBreeze wrote:

Thank goodness for this thread. It has saved my sanity. I had the same NaN error message as MarkCBB on many stacked column charts in a large report. Nothing I tried would fix the issue. I could create identical charts to the ones displaying the error, and they had no error! The only solution appeared to be to recreate all the problem charts :-(.

 

So I follow Mark's lead and look for a trendline. But my charts have non-numerical X axes and the Trendline option didn't appear in the Analytics pane. But if I changed the chart to a line chart and changed the X axis to something numerical (I used the Date column from my date table), the Trendline option appeared, a trendline was indeed present, and deleting it, then reversing the X-axis and chart type changes fixed the issue.

 

Apparently, the person who initially created the charts (not me) added trendlines, but then changed the chart type and X-axis. So the trendline was hiding there all the time causing the NaN warning, even though it was not accessible in the Analytics pane. Very tricky.


 

and now, I don't have the Color saturation working anymore on any levels lower than year, but I do have the level concatenating i.e. Year, year-Quater, year-Quater-Month etc.......

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.