Forum Discussion
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 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.
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.
8 Replies
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.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
Yep - you're right.
This should work tho:
MyMeasure = SUMX(VALUES('Calendar'[Date]),CALCULATE(DISTINCTCOUNT(Logs[Person]), Logs[Cat] in {"a", "c"}))
Hi smather
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
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?
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
