Measure Help - VAR Formula

[smather]

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 each day, for each person, where a certain marker appears. The VAR will need to work when filtered to year, month and even down to day & person. It'd be looking for either a or c in the cat; this can show more than once in a single day for the same person, but it would still only need to to be one 1 and not several. 

The data looks a little like this: 

Name	Date Time Start	Date Time End	Cat	Project
Person 1	01/01/2022 8:00	01/01/2022 10:00	a	123
Person 1	01/01/2022 10:00	01/01/2022 12:00	i	123
Person 1	01/01/2022 12:00	01/01/2022 12:30	b	N/A
Person 2	01/01/2022 07:00	01/01/2022 10:00	c	321
Person 2	01/01/2022 10:00	01/01/2022 17:00	c	321
Person 3	01/01/2022 11:00	01/01/2022 14:00	f	N/A

Expected outcome: Person 1 and 2 would show 1 each for the 01/01 but Person 3 would not. 

[v-yalanwu-msft · Accepted solution]

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.

[PhilipTreacy]

Hi @smather 

Download sample PBIX file

This measure works

Measure = 

VAR _x = CALCULATE(MINX('Table', [Date Time Start]), FILTER(ALL('Table'), 'Table'[Name] = MIN('Table'[Name])))
VAR _ac = MIN('Table'[Cat]) in {"a", "c"}

RETURN

SWITCH(

    TRUE(),

    _ac && (_x = MIN('Table'[Date Time Start])), 1,

    BLANK()
)

regards

Phil

[smather]

Hi @PhilipTreacy 

I've tried your VAR, and JirkaZ's formula below, but I don't get anything. I just have a blank box. There are filters on the page (to remove breaks/lunch) but even if I remove them, nothing happens 😞 

Would it be benifical to share dummy data PBIX?  

[PhilipTreacy]

Hi @smather 

Yes please, it's always beneficial to share the data/file you are working on so we are all working with the same data.

Regards

Phil

[smather]

Hi @PhilipTreacy 

See link below. I've removed most identifying information, but the underlying data is what I'm essentially working with. 

PBIX Report 

Thank you, I appreciate this!

[JirkaZ]

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]

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]

Yep - you're right.

This should work tho: 

MyMeasure = SUMX(VALUES('Calendar'[Date]),

CALCULATE(DISTINCTCOUNT(Logs[Person]), Logs[Cat] in {"a", "c"}

))