Forum Discussion

smather's avatar
smather
Helper III
4 years ago
Solved

Measure Help - VAR Formula

I need to do a measure and I'm a lil stuck on the best way to go about it.    Each person logs what they do in a day, and each person can have several entries per day. I need a VAR to assign 1 for ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, smather ;

    In your data, here is "a." and "c." ;not "a", "c".

    so you should change the measure.

    MyMeasure = 
CALCULATE(DISTINCTCOUNT('Table'[Full Name]), 'Table'[Cat] in {"a.", "c."})

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

JirkaZ's avatar
JirkaZ
Solution Specialist
4 years ago

Well the easiest would be something like this:

 

SUMX(VALUES(DimDate[Date]), 
   SUMX(VALUES(Person[Name]), IF(CONTAINSSTRING([Cat], "a") OR CONTAINSSTRING([Cat], "c"), 1, 0))
)

 

Just beware that with huge amount of rows it will get slow

smather's avatar
smather
Helper III
4 years ago

Hi JirkaZ 

 

thanks for replying!! I've tried your formula above, but as the Cat is a SWITCH column, it's not allowing me to reference it. And would this formula account for the fact that a or c could turn up more than once a day for the same engineer? 

  • JirkaZ's avatar
    JirkaZ
    Solution Specialist
    4 years ago

    Yep - you're right.

     

    This should work tho: 

     

    MyMeasure = SUMX(VALUES('Calendar'[Date]),
    CALCULATE(DISTINCTCOUNT(Logs[Person]), Logs[Cat] in {"a", "c"}
    ))