cancel
Showing results for 
Search instead for 
Did you mean: 
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

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.



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

Proud to be a Datanaut!




MarkCBB Helper V
Helper V

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 😞

Super User I
Super User I

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

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? Please mark it as a solution.

Proud to be a Datanaut!
Sean Community Champion
Community Champion

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 I
Super User I

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*


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Sean Community Champion
Community Champion

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 I
Super User I

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.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Sean Community Champion
Community Champion

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 I
Super User I

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

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


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors