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.

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