Count measure with conditions using VAR

[jeongkim]

Hi,

I need a measure to calculate with conditions that

Count: Internal Site ID

Condition 1: Category = NDPd

Condition 2: Site Creation(NDPd/IPM) = blank

Result= 23

My try is below and not resulting 23. still showing 32,561 total count of Internal Site ID.

Pls fix and suggest any simpler and better measure. 

NDPd Site Creation =

    VAR _count = COUNT('Distinct - Master Data'[Internal Site ID])

    VAR _filter = FILTER('Distinct - Master Data', 'Distinct - Master Data'[Category] = "NDPd" && 'Distinct - Master Data'[Site Creation(NDPd/IPM)] = BLANK())

    RETURN

        CALCULATE(_count, _filter)

[anmolmalviya05 · Accepted solution]

Hi @jeongkim, Please try the below measure:

NDPd Site Creation =
CALCULATE(
COUNTROWS('Distinct - Master Data'),
'Distinct - Master Data'[Category] = "NDPd",
ISBLANK('Distinct - Master Data'[Site Creation(NDPd/IPM)])
)

[Bibiano_Geraldo · Accepted solution]

Hi @jeongkim ,

You can achieve your goal by this DAX:

NDPd Site Creation = 
VAR _filter =
    FILTER(
        'Distinct - Master Data',
        'Distinct - Master Data'[Category] = "NDPd" &&
        ISBLANK('Distinct - Master Data'[Site Creation(NDPd/IPM)])
    )
RETURN
    CALCULATE(
        COUNT('Distinct - Master Data'[Internal Site ID]),
        _filter
    )

[Bibiano_Geraldo · Accepted solution]

Hi @jeongkim ,

You can achieve your goal by this DAX:

NDPd Site Creation = 
VAR _filter =
    FILTER(
        'Distinct - Master Data',
        'Distinct - Master Data'[Category] = "NDPd" &&
        ISBLANK('Distinct - Master Data'[Site Creation(NDPd/IPM)])
    )
RETURN
    CALCULATE(
        COUNT('Distinct - Master Data'[Internal Site ID]),
        _filter
    )

[jeongkim]

This solution also works thanks!

[anmolmalviya05 · Accepted solution]

Hi @jeongkim, Please try the below measure:

NDPd Site Creation =
CALCULATE(
COUNTROWS('Distinct - Master Data'),
'Distinct - Master Data'[Category] = "NDPd",
ISBLANK('Distinct - Master Data'[Site Creation(NDPd/IPM)])
)

[jeongkim]

It works thanks! 

[danextian]

Hi @jeongkim 

To achieve a distinct count filtered by _filter in DAX using CALCULATE, you must use the complete expression defined in the variable, rather than the variable itself. DAX variables are immutable. Once they're assigned a value, they cannot be altered or reassigned within the same expression.

[jeongkim]

any new dax suggestion pls? 

[danextian]

Just use the expression from the _count variable in CALCULATE after your RETURN statement.

[jeongkim]

I dont' get it, can you write the code? 

[danextian · Accepted solution]

Here you go

VAR _filter =
    FILTER (
        'Distinct - Master Data',
        'Distinct - Master Data'[Category] = "NDPd"
            && ISBLANK ( 'Distinct - Master Data'[Site Creation(NDPd/IPM)] )
    )
RETURN
    CALCULATE ( COUNTROWS ( 'Distinct - Master Data' ), _filter )

COUNTROWS - counts the number of rows in a table
COUNT - Counts the number of rows in the table where the specified column has a non-blank value

if there are no blanks in Internal Site ID column, the result will be the same