Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Change Measure formula based on filter condition

 

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. 

 

TYPEEMP COUNT DISTINCTDESIRED - EMP COUNT DISTINCT
F200200
P5016.50
Need Help
  • Arul's avatar
    Arul
    3 years ago

    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's avatar
    Anonymous
    3 years ago

    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 )
        )
    )

     

     

3 Replies

  • Arul's avatar
    Arul
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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"})

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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 )
        )
    )