topic Re: How do I make the number 0 show instead of blank values? in DAX Commands and Tips

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

e175429 — Fri, 29 Dec 2023 20:54:30 GMT

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

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

khaledHabib90 — Fri, 29 Dec 2023 23:32:36 GMT

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 🙂

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

jjrand — Sat, 30 Dec 2023 00:33:28 GMT

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.

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

salvalcaraz — Sun, 31 Dec 2023 15:19:11 GMT

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 )

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

jjrand — Sun, 31 Dec 2023 15:46:06 GMT

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.

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

salvalcaraz — Mon, 08 Jan 2024 10:54:09 GMT

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.

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

Dangar332 — Mon, 01 Jan 2024 15:59:23 GMT

Hi, e175429

In your measure add zero after calculation

[Measure]+0

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

e175429 — Tue, 02 Jan 2024 17:25:39 GMT

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"?

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

Dangar332 — Tue, 02 Jan 2024 17:27:24 GMT

hi, e175429

you can accept multiple answer as a solution