How to apply an IF ISBLANK with Filter

DAX DAX Calculations
1

Hi folks,

Can one of our learned colleagues suggest how I can apply an IF ISBLANK to the following construct:

dpeDISTINCTAdmins = CALCULATE(DISTINCTCOUNT('ADGroups'[SamAccountName]),FILTER('ADGroups','ADGroups'[IsDomainAdmin]="false" && 'ADGroups'[Domain]="DPE" && ADGroups[IsPriv]="true"))

TIA,
John

2

@jmaikido
Could you upload some sample data with an quick mock up of what the ideal solution would be?

Thanks,

Enterprise%20DNA%20Expert%20-%20Small

3

Hey Nick,

Nothing astounding …. basically I’m populating a Card visual, so if there’s data, obviously a value is displayed …… if not, I don’t want to see the word ‘blank’, rather ‘0’ (zero).

Regards,
john

4

Probably need to see a demo model of the scenario you are working with here to assist. The answer could be quite varied based on what you are looking to do and what the setup is in you model and the also the context of the calculation.

Thanks
Sam

5

Priv_Accounts_JUN19.xlsx (10.0 KB)

Ok, here’s a sample.
I have several card visuals …. each one displays a distinct count of accounts in different domains. One card to display a count for Domains AA1, AA2 etc.
I also have a filter visual that contains ONLY Domain names. again AA1, AA2 etc
If I select the Domain filter checkbox for AA4 …… ONLY the card visual for Domain AA4 data will display a count … and ALL the other cards will display the literal ‘(Blank)’.
So… for those cards where I have NO data (based on the filter selection), I want to display ‘0’ (zero) and NOT the word ‘(Blank)’
Hope that explains it.

TIA John

6

Going to need to see the demo in Power BI, so that all factors can be considered here.

Unfortunately the excel doesn’t really help at all.

There’s many things to consider here including the model you have, the data you have, the context of the calculation etc. So for the best help will need to see a demo example of what you are looking at.

If this is honestly as simple as turning a BLANK into a zero, this is quite easy.

All you need to do is write some logic like this.

IF( ISBLANK( measure ), 0, measure )

That’s it.

If you need more than this as mentioned would be helpful to actually see the model.

Thanks
Sam

2 Likes
7

H,

I only need to turn a (Blank) into a zero …… that’s why I included my original DAX …. I wanted to wrap my original syntax (below) with an IF(ISBLANK …. but unsure how to achieve it … doh!

dpeDISTINCTAdmins = CALCULATE(DISTINCTCOUNT('ADGroups'[SamAccountName]),FILTER('ADGroups','ADGroups'[IsDomainAdmin]="false" && 'ADGroups'[Domain]="DPE" && ADGroups[IsPriv]="true"))

Thanks and regards,
John

8

Ok sure, have I given you the idea now of what to do?

You can also place this measure into a variable and then use the IF logic in the RETURN section.

9

Hi @jmaikido,

This is one I hit all the time. I have had to create measures where I want a BLANK returned, and the also where I want a 0 if there is actually no records meeting the current filter context.

Here is a sample bit of code from my Revenue Insights report. The trick is the " + 0" after the DISTINCTCOUNT, which I believe will fit in nicely with your formula. I specifically use this for cards, so that stats don’t show that nasty “(blank)” value to the users.

Total Invoices = 
CALCULATE(
    DISTINCTCOUNT( ARRevenueDetail[InvRcptNumber] ) + 0
    , ARRevenueDetail[RevenueType] = "Invoiced"
)   //This forces a 0 to be displayed even if there are no records

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small

10

John,
Thanks for posting your code solution.
Regards, John