How do I make the number 0 show instead of blank values?

[e175429]

I have this graph. How do I get the number 0 to show instead of blank values?

[jjrand · Accepted solution]

Okay I have a solution, this is actually more difficult and convoluted than it has any right to be. It makes sense if you understand how filter context works, but there really should be an easier solution than this.

I used this video for reference, with a slight modification https://www.youtube.com/watch?v=mOgvAR-t90Y

Assuming your data is in the following format:

Category Year
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022

...

First, create two calculated tables with the following formulas

Categories = DISTINCT(Data[Category])

Years = DISTINCT(Data[Year])

Second, create relationships as shown below

Third, create this measure

Measure =

    COUNTX(

        Data,

        Data[Category]

    ) + 0

Last step, and this is important, put Category from the Categories calculated table, and Year from the Years calculated table into rows and columns as shown below. Do not put them from the original source table, it will not work. Put Measure into values.

Congratulations, you now have 0's instead of blanks.

[Dangar332 · Accepted solution]

Hi, @e175429 

In your measure add zero after calculation

Like

[Measure]+0

[salvalcaraz]

You just need to add a simple control over blanks values to your original measure, there's no need for anything more complex than that:

Measure_With_No_Blanks =
IF( ISBLANK(YourOriginalMeasure), 0, YourOriginalMeasure )

This formula checks if the original measure returns blank. If it does, it substitutes it with 0; otherwise, it returns the original measure value. Make sure to replace <YourOriginalMeasure> with the actual name of the measure.

In case you are working directly with the field, then you need to actually create the measure:

Measure_With_No_Blanks =
IF( ISBLANK(YourField), 0, YourField )  

[e175429]

@jjrand, @khaledHabib90 IDK if it's becasue of the way my data is set up but, @Dangar332 reply above solved my issue.

@jjrand I followed your instructions to the "T" and that way worked too. Can I select multiple responses as "Accepted Solution"?

[Dangar332]

hi, @e175429 

you can accept multiple answer as a solution

[jjrand]

Hello,

This does not always works, and assuming the data is in a certain format, it would not work in this case (original question didn't provide the data). I have tried the simple approach and the result is shown below. There are still blank values.

This occurs because the IF statement has nothing to evaluate against, since there simply are no Recruitment and Surveys entries for 2022.

This is why we have to create the relationships as shown in my answer below, to force that evaluation.

[salvalcaraz]

In my answer I made the assumption that you already had a calendar table, which is not a best practice, but a "must" while modeling. Your issue isn't in DAX, but in modeling your data.

Here you have a simple model, just by defining a calendar table and building the relationship towards your fact table should be enought. You can create your calendar table with this DAX code:

Date =
VAR MinYear = YEAR ( MIN ( Data[Year] ) )
VAR MaxYear = YEAR ( MAX ( Data[Year] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Calendar Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] )
)

After that, don't forget to mark this table as your date table in the model view.

[jjrand · Accepted solution]

Okay I have a solution, this is actually more difficult and convoluted than it has any right to be. It makes sense if you understand how filter context works, but there really should be an easier solution than this.

I used this video for reference, with a slight modification https://www.youtube.com/watch?v=mOgvAR-t90Y

Assuming your data is in the following format:

Category Year
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022
Events 2022

...

First, create two calculated tables with the following formulas

Categories = DISTINCT(Data[Category])

Years = DISTINCT(Data[Year])

Second, create relationships as shown below

Third, create this measure

Measure =

    COUNTX(

        Data,

        Data[Category]

    ) + 0

Last step, and this is important, put Category from the Categories calculated table, and Year from the Years calculated table into rows and columns as shown below. Do not put them from the original source table, it will not work. Put Measure into values.

Congratulations, you now have 0's instead of blanks.

[khaledHabib90]

If you are using a measure, then you need to wrap your measure inside an if(isblank()

As the below

If(isblank(YourMeasureHere),0,YourMeasureHere)

If i answered your question, please accept my reply as the solution 🙂