Change Measure formula based on filter condition

[Anonymous]

How do I write a DAX measure based on a condition on 'TYPE' column? If TYPE = P, then count distinct employee IDs times .33 else just diplay count distinct employees. This measure will be used on a layout that will not have the 'TYPE' column in it. 

TYPE	EMP COUNT DISTINCT	DESIRED - EMP COUNT DISTINCT
F	200	200
P	50	16.50

[Arul · Accepted solution]

@Anonymous ,

try this

Emp count distinct = 
VAR _disctinctCount = DISTINCTCOUNT('Sample Table'[employeeID])
VAR _result = IF(SELECTEDVALUE('Sample Table'[Type]) = "P",_disctinctCount*0.33,_disctinctCount)
RETURN _result

Thanks,

Arul

[Anonymous · Accepted solution]

Hello @Arul  and the forum!

When the 'Type' column is not being used on the layout. The measure is not doing the math. Therefore, I resolved the issue with the dax code below.

Active FTEs (Cal_2) =
VAR _P =
    CALCULATE (
        DISTINCTCOUNT ( EMP_TRAN_FACT[HR Employee ID] ),
        EMP_TRAN_FACT[HR Employee Event Type] = "ACTIVE"
            && EMP_TRAN_FACT[HR Employee Job Type] = "P"
    )
VAR _F =
    CALCULATE (
        DISTINCTCOUNT ( EMP_TRAN_FACT[HR Employee ID] ),
        EMP_TRAN_FACT[HR Employee Event Type] = "ACTIVE"
            && EMP_TRAN_FACT[HR Employee Job Type] = "F"
    )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( EMP_TRAN_FACT[HR Employee Job Type] ),
            IF ( EMP_TRAN_FACT[HR Employee Job Type] = "P", _P * 0.33, _F )
        )
    )

[Arul · Accepted solution]

@Anonymous ,

try this

Emp count distinct = 
VAR _disctinctCount = DISTINCTCOUNT('Sample Table'[employeeID])
VAR _result = IF(SELECTEDVALUE('Sample Table'[Type]) = "P",_disctinctCount*0.33,_disctinctCount)
RETURN _result

Thanks,

Arul

[Anonymous]

Thanks a lot @Arul . Works perfectly! Learnt something new today 🙂

I just had to tweak the first variable to filter the data. 

VAR _disctinctCount = CALCULATE(DISTINCTCOUNT('Sample Table'[employeeID]), 'Sample Table'[Column] in {"abc", "xyz"})