Replace "blank" with 0

[Anonymous]

Hi Community

I know this have been posted so many times but have not found a solution that really works in my example. I hope you are able to help me.

I have created a Table visualization that contains data from 2 files which both contains the "ID" column. File 1 contains all "ID's" where file 2 only contains some of the "ID's". 

In this example file 1 contains 4 lines where ID is "DM122067" and sum (4) is shown in the "Delivered" column. But file 2 do not contain that ID and therefore the measure for "Click Through Rate" and "Click To Open Rate" is shown as blanks.

To calculate "Click Through Rate" I am using this:

Click Through Rate =

DIVIDE(

    DISTINCTCOUNT('Email - Clicks'[ACS ID]),

    COUNTA('Email - Delivery'[ID])

)

How can I get 0 if result is blank?

[PhilipTreacy]

Hi @Anonymous 

So my suggestion to +1 the result of the CTR Measure is actually working.  It's just that your table is then behaving in ways that you don't want.  That's a different problem.

Looking at your data you have multiple Many-to-Many relationships on the ID field and the only relationship tieing a Sales Company to other tables is via Preferred Language.

So I'd say there is some issue(s) with the way your data is set up and the relationships between tables.

You might be better off starting another post and restating the issue which is when you return 0 from CTR the table messes up.

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Using your own PBIX file I don't have a problem it works as expected.  If I force DIVIDE to return BLANK(), by adding 0 to that result I get 0 for CTR.

Click Through Rate = 

DIVIDE(
	DISTINCTCOUNT('Email - Clicks'[ACS ID]),
	//COUNTA('Email - Delivery'[ID])
    BLANK()
) + 0

If you are concerned about the red column in your tablew, that is because CTR is now returning 0 instead of BLANK and your conditional formatting is set to show red with a value of 0.

When you say my suggestions are not working, what exactly isn't working?  What result are you getting that you are not expecting?

Regards

Phil

[Anonymous]

The below is using an example where the table is filtered to only show ID = DM129369

When I am not adding zero for CTR this is what I see - and are correct:

When adding the zero to the measure I get this result:

As you might see I suddenly get a lot a "Sales Companies" which I should not get as DM129369 is only for Sales Company = GIN

[v-shex-msft]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi

Unfortunately the solution do not work. As I do not know why is does not work with my dataset I am not sure how to add any further detailed information.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download sample PBIX

I've set up some dummy data and adding 0 to the measure gives me 0 when the result is blank.

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Can you share your file or some sample data so I can test?

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

So you've tried adding 0 to your calculation result

Click Through Rate =
DIVIDE(
    DISTINCTCOUNT('Email - Clicks'[ACS ID]),
    COUNTA('Email - Delivery'[ID])
) + 0

Or using IF

Click Through Rate =
VAR _CTR =
DIVIDE(
    DISTINCTCOUNT('Email - Clicks'[ACS ID]),
    COUNTA('Email - Delivery'[ID])
)

RETURN

IF(_CTR = BLANK(), 0, _CTR)

Regards

Phil

[Anonymous]

@PhilipTreacy Unfortunately neither of your 2 suggestions are working. When I use them my table totally f.... up 🙂

Do you have another suggestion?