topic Re: Need my KPI card to say 0 instead of "Blank" in DAX Commands and Tips

Need my KPI card to say 0 instead of "Blank"

e175429 — Fri, 28 Jul 2023 16:13:15 GMT

This is what I want to say:

"How many Administrative are Men?"

This is my DAX:

M Administrative Associate = CALCULATE([Total Employees], FILTER(cleaned,cleaned[Position Descr] = "ADMINISTRATIVE ASSOCIATE"), FILTER(cleaned,cleaned[Sex] = "M")

And that works fine.

HOWEVER, since there are no males in this group I get "Blank" on the KPI card. I need it to just say 0, the actual number instead of blank.

This is my DAX:

M Administrative Associate = IF(ISBLANK(CALCULATE([Total Employees],

FILTER(cleaned,cleaned[Position Descr] = "ADMINISTRATIVE ASSOCIATE"), FILTER(cleaned,cleaned[Sex] = "M"), 0,

CALCULATE([Total Employees], FILTER(cleaned,cleaned[Position Descr] = "ADMINISTRATIVE ASSOCIATE"), FILTER(cleaned,cleaned[Sex] = "M")))))

And of course this is not working because I suck at DAX 😞

Re: Need my KPI card to say 0 instead of "Blank"

Take3 — Fri, 28 Jul 2023 16:33:37 GMT

e175429

Please try the following.

Place your measure inside of VAR1.

Then create a second VAR2 that checks if VAR1 is blank, and returns 0 if it's blank.

Something like the following.

Hope this is helpful to you.

Regards,

Nathan

P.S. Although it is possible to go the route you wrote it, it is not recommended. Both for performance & readability, any time you repeat the same code multiple times within the same measure, it is best practice to place that code inside a VAR. (In the code below, the exact same CALCULATE appears twice.)

Re: Need my KPI card to say 0 instead of "Blank"

e175429 — Fri, 28 Jul 2023 17:47:02 GMT

Hey,

I am not sure what "Result" should be in VAR2

Re: Need my KPI card to say 0 instead of "Blank"

Take3 — Fri, 28 Jul 2023 19:18:05 GMT

Sorry to be confusing. VAR1 & VAR2 are just ideas that represent 2 different variables.

You can call them whatever you want.

- VAR1 contains your original CALCULATE statement (but only 1 time).

- VAR2 contains IF logic that checks to see if VAR1 is blank.

- IF VAR1 is blank THEN return 0 ELSE return VAR1.

Re: Need my KPI card to say 0 instead of "Blank"

e175429 — Fri, 28 Jul 2023 19:53:58 GMT

No, you're not confusing, I just don't know what I'm doing. I just started using this service.

However, your advice worked so I am very appreciative to you. Thank you so much!