Stacked Column Chart - Too Many Values for 130+ Datapoints - For Text or Categorical X-axis

[marcin71]

Hi,

Symptoms:

Create a Stacked Column Chart with text-based X-axis values or numeric X-axis values in Type Categorical mode.

E.g.:

- Text: r1801, r1802, r1803 ... r1812, r1901, …

- Or numerical: 1810, 1811, 1812, 1901, 1902, … in Categorical Type of PBI

When a total number of datapoints in all columns is below about 130, then everything's fine.

When the number of datapoints exceeds this threshold, e.g. a new column added or more datapoints are added to existing columns, then:

- 'Too Many Values' notice is displayed

- displayed results become unreliable, because datapoints are 'stolen' from existing columns in order to accommodate the overall number of 130; as a result some columns displayed correctly before adding new datapoints become 'smaller' for no obvious reason (!).

Notice:

This behavior does not occur if:

- with text X-values the overall number of datapoints is lower than about 130

- numerical X-values are used in Continuous Type of PBI, e.g. 1810, 1811, 1812, 1901, 1902,… – but then naturally  1812 column is separated by a large void space from 1901 column.

Please help to fix this issue as it makes this kind of reports unusable for our organization.

Kind regards,

Marcin

[v-qiuyu-msft]

Hi @marcin71,

I have tested in the latest Power BI desktop version 2.64.5285.741 with more than 130 datapoints (151 records) but not reproduce the issue. You can test our report to see if the issue occurs on your side. 

If possible, please share your pbix file with us. You can upload it to your OneDrive and send the share link to me via private message. 

Best Regards,
Qiuyun Yu 

[marcin71]

Hi @v-qiuyu-msft

Thanks for checking that. I managed to pinpoint the root cause in the meantime. It isn't casued by the number of datapoints as such, but by a number of unique items displayed in Y-axis. When there are more than 60 unique items in the report, the 'Too Many Values' notice appears. Check out the following scenarios:

Correct situation: text values in X-axis, 59 unique items as Y-values, no error

The same setup, but 61 unique items in Y, 'Too many values' error:

And now the same chart but with Numerical values in X-axis, Type Continuous, 65 unique items in Y, no error (!)

Finally, the same chart, Numerical X-axis but the Categorical Type, 65 unique items: NOK (!)

It proves that the problem described originally is still valid, although it isn't related to 130 datapoints, but rather to a threshold of 60 unique value types. 

Since it all works fine for numerical/continuous X-axis values could you fix it for text or numerical/categorical X-axis type? 

I'll send you my sample datasets & PBI in a priv message. All tests were done with the latest PBI version, the same as yours.

Many thanks!

Marcin

[v-qiuyu-msft]

Hi @marcin71,

I have reported this issue internally: CRI 93770467. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @marcin71,

I got information below internally: 

"This is not an issue. It is a result of using different data reduction strategies depending on the configuration of the chart and it is by design.

The below explanation applies to imported models.

If the chart X axis is continuous, we will try to dynamically (at query runtime) balance the number of values between the X axis and the legend. For the X axis we request a Sample, which will pick certain values only and for the Legend we request the Top values. Legend has 66 distinct values. Axis has 20 distinct values. We will try to balance up to 3500 total points. Because 66 x 20 = 1320 we will fit all the data in the visual.

If the chart X axis is categorical, we switch the strategy to load segments of data for the axis and have a hard limit of Legends (60). This allows scrolling of the X axis where we can load more windows of data. However, we need to preserve the same number of Legend items as we scroll, so we have a hard limit of 60 values. Since there are 66 values for the legend, we of course exceed this limit and show the warning.

We are in the process of improving our documentation for the visual data limits. Once that is done it will be publicly available for that customers can have a better understanding of what happens behind the scenes."

Best Regards,

Qiuyun Yu