Need a [Measure] - Countrows by several categories, AND evaluating booleans

[grggmrtn]

My data:

PersonId	ServiceName	Year	WeekNr	Weekday	Pause
1	Paper	2021	50	monday	False
1	Paper	2021	50	tuesday	False
1	Wood	2021	51	monday	True
1	Wood	2021	51	tuesday	True
2	Paper	2022	4	monday	False
3	Tomato	2022	2	monday	True
3	Tomato	2022	2	tuesday	False
3	Tomato	2022	3	monday	False
3	Tomato	2022	3	tuesday	False

So - I need to find two things in order to get the result I need.

1. I need the row count per [PersonId,ServiceName,Year and Weeknr]. It should end up being no more than 2 (because we only use monday and tuesday)
2. I need to know if at least one of the boolean values per [PersonId,ServiceName,Year and Weeknr] (same group as before) is False.

If A = 2 and B = True then the person gets billed (Billed = 1 else 0)

Meaning, for the above table:

PersonId	ServiceHame	Year	WeekNr	Billed (measure)
1	Paper	2021	50	1
1	Wood	2021	51	0 (2x True)
2	Paper	2022	4	0 (only 1 row)
3	Tomato	2022	2	1
3	Tomato	2022	3	1

I'd love this to work in a measure, since I'm working off a PBI Dataset.

Can anyone lead me in the right direction?

[Fowmy · Accepted solution]

@grggmrtn 

Can you try this measure please:

Billed = 
VAR __PAUSE = VALUES( Table2[Pause] ) 
RETURN
IF ( 
     COUNTROWS(Table2) = 2 && FALSE() IN __PAUSE , 
    1,
    0
)

[grggmrtn]

I'm seriously impressed! That was a LOT easier than I had imagined, and I'm still not sure why it's working, but it is 😉

Is there any way to do this with a SUMMARIZE instead of an actual "physical" table? My data, in reality, is comprised of a factless fact table and a bunch of dimensions...

[Fowmy]

@grggmrtn 

Glad it solved your problem!

How it works ?
VALUES captures the currently visible  values from the Pause column and stores in __PAUSE virtual table, the IF condition checks if there are two rows in the table, and using the  IN operator it also checks if FALSE() value exists within __PAUSE table, then returns 1 else 0

Hope I it was clear or confused you 🙂

Regarding your 2nd question, yes, you can perform this using a virtual table as well, it depends on the type of model and tables involved.

[grggmrtn]

Yeah I'm having big problems getting it virtual - The measure that I actually got to WORK (but give wrong results was

Afregning =
VAR Pause = VALUES(FACT_Indsatser[PauseFlag])
VAR Tab = COUNTROWS(
    CALCULATETABLE(
        SUMMARIZE(
            FACT,
            Person[PersonId],
            Dato[YearWeek],
            Dato[WeekdayName],
            Department[DepartmentName],
            Service[SerciceName],
            FACT[Pause]),
        Dato[Ugedagnavn] = "Mandag" || Dato[Ugedagnavn] = "Tirsdag" && FALSE() IN Pause
    )
)
RETURN
IF(Tab = 2, 1, 0)

(The CALCULATETABLE part is what worked for me, and gave me the correct values earlier, when I used it to create an actual "physical" table)

It seems a lot of the problem COULD be that the calculated table isn't getting filtered with the filters that I've put on the actual dimensions.

It could also be that I have no idea what I'm doing when it comes to virtual tables...