How to show every category in the chart, not only the ones that have values in them?

[MikhailGG]

Hello.

I have the following table for benchmarks of height in inches: 

Lets say I'm calculating the number of dogs that fall under each of the category using the following formula:

Dogs_height =

dogs = Table['dogs']

    return CALCULATE (VALUES ( 'Benchmark Categories'[Categories]), dogs>='Benchmark Categories'[Lower_Level],dogs<='Benchmark Categories'[Upper_Level])

The issue with that is that I'm getting the results only for those that fall under any of the categories, but if there are no values for any specific category, then I get nothing.

What I would like to get is all of the categories in the chart, but when there is no values in any particular category, then show it as 0 rather than not including it. E.g. 121-130 category will have 0, so as 141-150, etc.

[KNP · Accepted solution]

Right, makes sense.

To model it "correctly", I'd probably do this with the Benchmark Categories.

Here's the code for reference...

let
    // Source data from a compressed Base64 JSON string
    Source = 
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WMlDSUTI0UIrViVYyNASyjSBsIxDbGMI2BrFNIGwTENsUyI4FAA==", 
                        BinaryEncoding.Base64
                    ), 
                    Compression.Deflate
                )
            ), 
            let
                _t = ((type nullable text) meta [Serialized.Text = true])
            in
                type table [#"Lower Level" = _t, #"Upper Level" = _t]
        ),
    
    // Add a custom column "Categories"
    AddedCategories = 
        Table.AddColumn(
            Source, 
            "Categories", 
            each [Lower Level] & "-" & [Upper Level]
        ),
    
    // Add an index column
    AddedIndex = 
        Table.AddIndexColumn(
            AddedCategories, 
            "Index", 
            1, 
            1, 
            Int64.Type
        ),
    
    // Change column types
    ChangedType = 
        Table.TransformColumnTypes(
            AddedIndex, 
            {
                {"Lower Level", Int64.Type}, 
                {"Upper Level", Int64.Type}, 
                {"Categories", type text}
            }
        ),
    
    // Add a custom column "Height" with a list of numbers from Lower Level to Upper Level
    AddedHeight = 
        Table.AddColumn(
            ChangedType, 
            "Height", 
            each {Number.From([Lower Level])..Number.From([Upper Level])}
        ),
    
    // Expand the list in the "Height" column
    ExpandedHeight = 
        Table.ExpandListColumn(
            AddedHeight, 
            "Height"
        ),
    
    // Change the type of the "Height" column to Int64
    ChangedTypeHeight = 
        Table.TransformColumnTypes(
            ExpandedHeight, 
            {{"Height", Int64.Type}}
        )
in
    ChangedTypeHeight

Once you have that table exploded, join Height to the height in dogs and then use the Categories column from the Benchmark table in your visual and the method I mentioned previously to ensure all categories show. 

It will likely also help simplify the DAX required.

[MikhailGG]

That worked. Thanks.

Follow up question:

What if I want to use the same field heights and measure other fields in the same table? 

Originaly it was height, but what if I'll include weight and age fields to the dog table. How do I make a relationship if I want to use the same benchmark table and same "Height field" to measure weight and age in addition to heigh?.

[KNP]

Honestly, I was focussed on solving the initial problem. Is it working? I thought I saw a message from you saying it's not?

To answer your follow-up, without looking at the full picture, I'm not sure how I would model it. You can create buckets for reporting and may not need the dimension tables but I prefer to create the dimensions as it makes the reporting and DAX easier.

I probably wouldn't try to keep it all in the same benchmark table and may create new versions for weight and age and join to the dog table, but like I say, without having the full picture, it's hard to say.

[KNP]

If I understand correctly, if you right click on one of your fields in the field well, you can turn on 'Show items with no data'. I think that'll give you what you're looking for.

[MikhailGG]

Hi @KNP 

I tried that but it does nothing.

It's like it only accounts for the data in the categories.

[KNP]

Can you add a screenshot of your data model/relationships?

[MikhailGG]

There is no relationship.

I created a custom example. Essentially I have 2 tables:

Benchmark Categories:

Dogs: 

However, I still miss the 11-20 category: