cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Patron

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

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?

1 ACCEPTED SOLUTION
Super User

@grggmrtn

Can you try this measure please:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
4 REPLIES 4
Super User

@grggmrtn

Can you try this measure please:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Patron

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

Super User

@grggmrtn

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.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Patron

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

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors