Forum Discussion
Replace "blank" with 0
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:
Hi Anonymous
I've set up some dummy data and adding 0 to the measure gives me 0 when the result is blank.
Regards
Phil
9 Replies
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]) ) + 0Or 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
PhilipTreacy Unfortunately neither of your 2 suggestions are working. When I use them my table totally f.... up 🙂
Do you have another suggestion?
Hi Anonymous
Can you share your file or some sample data so I can test?
Regards
Phil
Hi Anonymous
I've set up some dummy data and adding 0 to the measure gives me 0 when the result is blank.
Regards
Phil
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
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() ) + 0If 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
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
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