cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Super Contributor
Super Contributor

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

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

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

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 Member
Member

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

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

KHorseman Super Contributor
Super Contributor

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

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

Proud to be a Datanaut!
SteelBreeze Frequent Visitor
Frequent Visitor

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

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.

tiztrain Regular Visitor
Regular Visitor

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

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.


 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)