cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joycelyn
Frequent Visitor

Stop a "Zero" value displaying as (blank)

Hello

I have a stacked column chart that is displaying a blank column and I cannot figure out why. Here is the chart, Filters and Visualisations panes:  

Joycelyn_0-1633520009086.png

 

Joycelyn_1-1633520009090.png

 

Along the x-axis are the number of illnesses - Comorbidities_Present - that patients have. The count is the number of patients with the stated number of illnesses. This is a number ranging from 0 to 6 as shown in the screenshot below. It does not contain blank (empty) values.

Joycelyn_2-1633520009094.png

 

The legend is the Time Period in which the patients were diagnosed. It also doesn’t contain any blank (empty) values. The table above shows that this is a number from 1 to 3. It is related to the table below.

Joycelyn_3-1633520009095.png

 

I have another table that is related to the Comorbidities_Present column which ‘translates’ the numbers of illnesses to a word (EN Number), so that 0 = “None”, 1 = “One”, 2 = “Two” etc. However, the chart is not displaying the word “None” and I can’t figure out why.

Joycelyn_4-1633520009097.png

 

I’ve tried changing the Legend to the Index column of Category Scales and selected to show the values 0 to 7, but the result is the same (see screenshots below). I’ve also tried toggling between turning on and off ‘show items with no data’, but it doesn’t appear to make a difference. I’ve seen a previous post that says to change the x-axis to continuous, but I don’t have this option in my version of Power BI Desktop (Version: 2.88.2361.0 64-bit (januari 2021))

Joycelyn_5-1633520009099.png

 

Joycelyn_6-1633520009101.png

 

1 ACCEPTED SOLUTION
Joycelyn
Frequent Visitor

Hi Pete,

I agree that it’s a relationship mismatch. There is another relationship between the dimension and fact table and that was causing the problem. The calculation on [Comorbities_Present] can only be a round number because it only counts the individual values in a column containing semi-colon separated values e.g. ‘2;5;6’. I have made a new dimension table specifically for translating numbers to text. Now ‘Zero’ is shown:

Joycelyn_0-1633601725951.png

 

And so that I don’t need to keep repeating HEX codes, I will make a dimension table for that and link it to the category_scales and the new table.

 

Thanks for your help. I’m working alone here, so it’s been nice to bounce ideas off another person, rather than just speaking into the ether 😊

View solution in original post

7 REPLIES 7
Joycelyn
Frequent Visitor

Hi Pete,

I agree that it’s a relationship mismatch. There is another relationship between the dimension and fact table and that was causing the problem. The calculation on [Comorbities_Present] can only be a round number because it only counts the individual values in a column containing semi-colon separated values e.g. ‘2;5;6’. I have made a new dimension table specifically for translating numbers to text. Now ‘Zero’ is shown:

Joycelyn_0-1633601725951.png

 

And so that I don’t need to keep repeating HEX codes, I will make a dimension table for that and link it to the category_scales and the new table.

 

Thanks for your help. I’m working alone here, so it’s been nice to bounce ideas off another person, rather than just speaking into the ether 😊

Cool, glad you've got it sorted.

Always happy to help. 🙂

 

Pete

BA_Pete
Super User
Super User

Hi @Joycelyn ,

 

The quickest way to identify the offending data entries would be to create a table on your report page that contains the data that is driving the chart, making sure to include in your table the field to which your broken dimension table is related. Then click on the (Blank) axis label and it should filter your table to only the rows that your chart considers to be (Blank). Once you have the offending entries, chances are identifying and solving the issue will be quick work.

 

Some common reasons for (Blank) dimension to show:

- Your dimension table doesn't hold all the values that feature in the fact table that it is related to.

- Your fact or dimension table contains a null value

- Your fact or dimension field has been left as 'Untyped' from Power Query and there are mixed data types e.g. 0 and "0".

 

FYI: The reason you are not getting the option to change to a continuous axis is because you are using a stacked chart. If you removed one of the stacks, or changed to a clustered chart, it would allow you to change this attribute. However, this isn't going to fix your issue here. Identify the offending rows as above and fix the problem at source.

 

Pete

Joycelyn
Frequent Visitor

Hi @BA_Pete

Thanks for the tip. I’ve done as you say (see below) and I’m thinking that the solution must relate to either or 2nd or 3rd suggestion. Namely, ‘Your fact or dimension table contains a null value’ or ‘Your fact or dimension field has been left as 'Untyped' from Power Query and there are mixed data types e.g. 0 and "0".’

Joycelyn_0-1633524670759.png

I’ve rechecked the data type of the columns ‘Category Scales’[Index] and [Comorbidities_Present] and they are both set to ‘Whole Number’. Do you have any suggestions on what I should try next?

Hi @Joycelyn ,

 

The extra table to be added to your report should be the raw data in your fact table, not just the chart in table format.

In that new table, put the following fields:

'Category Scales'[Index]

'Category Scales'[EN Number]

'ICHOM Patient'[Index or Unique ID field]

'ICHOM Patient'[Comorbidities Present]

yourLegendTable[Index]

(if this last one gives you a relationship error, then create another new table with just yourLegendTable[Index], 'ICHOM Patient'[Index or Unique ID field], and 'ICHOM Patient'[Comorbidities Present] in it).

 

Now click on the blank axis in your chart and see which exact fact rows are giving you the issue.

 

Pete

Joycelyn
Frequent Visitor

Hi @BA_Pete

I think that I’ve done what you suggest. The table below is filtered to show all rows where the EN Number is blank. Currently I can’t see what the relation is between these rows. I also think that some rows are being counted more than once (?). I’m going to sleep on it and see if the answer comes to me tomorrow.

Joycelyn_0-1633529341794.png

 

Incidentally, if I don’t use EN Number as the x-axis and use Comorbidities_Present instead, then no blank values are shown. Maybe I’m asking my data to make one jump too many 🤔

Joycelyn_1-1633529341795.png

Thanks for the help,

Joycelyn

 

@Joycelyn ,

 

Ok, no worries.

 

Interesting that your chart works fine with [Comorbities_Present] but not with [EN Number]. This suggests a relationship mismatch between your dimension and fact tables.

 

Also interesting that you values in your last chart for '1' [Comorbities_Present] do not match the values in your original chart for 'One' [EN Number]. This suggests that your mismatches occur across all values, not just the missing ones (zero, five, six) from your original chart.

 

As the [Comorbities_Present] column is the result of a calculation, I'm wondering whether some of the outputs are actually 3.0000002 or similar, and therefore not matching with your dimension index properly.

Try wrapping your whole countValues variable with a ROUND function, something like this:

VAR __countValues =
ROUND( [your current calculation], 0)
RETURN
...

 

Pete

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Users online (701)