Reply
Member
Posts: 125
Registered: ‎03-07-2016

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

 

 

 

 

Super User
Posts: 755
Registered: ‎08-27-2015

Re: Chart data contains not a number (NaN) values

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.

Member
Posts: 125
Registered: ‎03-07-2016

Re: Chart data contains not a number (NaN) values

@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 Smiley Sad

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Chart data contains not a number (NaN) values

[ Edited ]

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?

Super User
Posts: 2,146
Registered: ‎08-11-2015

Re: Chart data contains not a number (NaN) values

@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) ???

 

 

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Chart data contains not a number (NaN) values

@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*

Super User
Posts: 2,146
Registered: ‎08-11-2015

Re: Chart data contains not a number (NaN) values

@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???

 

 

 

 

 

 

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Chart data contains not a number (NaN) values

@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.

Super User
Posts: 2,146
Registered: ‎08-11-2015

Re: Chart data contains not a number (NaN) values

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

 

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

Highlighted
Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Chart data contains not a number (NaN) values

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