Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grggmrtn
Post Patron
Post Patron

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

My data:

PersonIdServiceNameYearWeekNrWeekdayPause
1Paper202150mondayFalse
1Paper202150tuesdayFalse
1Wood202151mondayTrue
1Wood202151tuesdayTrue
2Paper20224mondayFalse
3Tomato20222mondayTrue
3Tomato20222tuesdayFalse
3Tomato20223mondayFalse
3Tomato20223tuesdayFalse

 

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:

PersonIdServiceHameYearWeekNrBilled (measure)
1Paper2021501
1Wood2021510 (2x True)
2Paper202240 (only 1 row)
3Tomato202221
3Tomato202231

 

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
Fowmy
Super User
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
)

Fowmy_0-1642600365670.png

 



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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User
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
)

Fowmy_0-1642600365670.png

 



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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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




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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.