Forum Discussion
Stop a "Zero" value displaying as (blank)
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:
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 😊
Super UserHi Anonymous ,
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
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".’
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?
- BA_Pete
Hi Anonymous ,
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
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.
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 🤔
Thanks for the help,
Joycelyn
- BA_Pete
Anonymous ,
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