topic Re: Help Replacing Blank Values with 0 in DAX! in DAX Commands and Tips

Help Replacing Blank Values with 0 in DAX!

Anonymous — Thu, 17 Oct 2024 16:55:19 GMT

I am calculating the retention rate of our customers.

In the Power BI matrix visual, the columns represent the Day Count, and the rows show the Registration date. To illustrate, I am analyzing the cohort that registered on September 21st. On day 0, 38 people from that cohort visited our shop. On day 1, 10 people visited, and on day 2, 2 people visited. However, from day 3 to day 15, no one from that cohort visited, so the cells display blank values.

In DAX, I want to replace these blank values with 0. I have tried using COALESCE(), ISEMPTY, NOT(ISBLANK), and adding +0, but none of these approaches are working. How can I modify the following measure to return 0 instead of blank?

Number of Customer Visits =
CALCULATE( COUNT(TableA[CustomerID]) )

Thank you very much!😭

Re: Help Replacing Blank Values with 0 in DAX!

lbendlin — Thu, 17 Oct 2024 19:34:45 GMT

Number of Customer Visits =
COALESCE(CALCULATE( COUNT(TableA[CustomerID]) ),0)

Re: Help Replacing Blank Values with 0 in DAX!

SachinNandanwar — Thu, 17 Oct 2024 19:38:13 GMT

Just a wild guess.try something like this

Customer Vists = CALCULATE(SWITCH(TRUE(),NOT ISBLANK(COUNT(TableA[CustomerID])),COUNT(TableA[CustomerID]),0))

Re: Help Replacing Blank Values with 0 in DAX!

Anonymous — Fri, 18 Oct 2024 06:14:45 GMT

:'( thank you for your help but it doesn't work

Re: Help Replacing Blank Values with 0 in DAX!

Anonymous — Fri, 18 Oct 2024 06:15:30 GMT

Any other alternative to COALESCE(), ISEMPTY, NOT(ISBLANK), and adding +0? These didnt work

Re: Help Replacing Blank Values with 0 in DAX!

divyed — Fri, 18 Oct 2024 08:18:28 GMT

Hello Anonymous ,

You can use IF condition to return desired value , something like :

NO_OF_CUST =

VAR _CUst = CALCULATE(SUM(T_Registration[Cust_ID])) // Replace with your measure, this is on dummy data

RETURN

IF (ISBLANK(_CUst)=TRUE(),0,_CUst)

You can replace your actual measure in if condition and should work

If this helps, please mark this as a solution. You can say special thank you by giving Kudos 🙂

Cheers.

Re: Help Replacing Blank Values with 0 in DAX!

Anonymous — Sun, 20 Oct 2024 11:21:15 GMT

Thank you for dedication. Unfortunately it couldn't work on my report

Re: Help Replacing Blank Values with 0 in DAX!

divyed — Sun, 20 Oct 2024 11:41:42 GMT

Hello Anonymous ,

Strange, have you checked if correct relationship is setup ?

Ok, lets try couple of more things :

1. Use COUNTROWS with Values : This approach will return 0 where no customers exist

Number of Customers visited =
IF(
COUNTROWS(VALUES(TableA[CustomerID])) = 0,
0,
COUNT(TableA[CustomerID])
)

Number of Customers visited=
SUMX(
VALUES(Day_Count), // replace with actual field name
IF(COUNT(TableA[CustomerID]) = BLANK(), 0, COUNT(TableA[CustomerID]))
)

Please let us know if this works.

Cheers

Re: Help Replacing Blank Values with 0 in DAX!

Anonymous — Mon, 21 Oct 2024 07:24:57 GMT

Hi Anonymous ,

You can try formula like below:

Number of Customer Visits = IF ( ISBLANK ( MAX(TableA[Customer Visits])), 0, CALCULATE ( SUM( ( TableA[Customer Visits] ) ) ))

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.