Forum Discussion

grggmrtn's avatar
grggmrtn
Post Patron
4 years ago
Solved

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?

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    grggmrtn 

    Can you try this measure please:

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

     



4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    grggmrtn 

    Can you try this measure please:

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

     



    • grggmrtn's avatar
      grggmrtn
      Post Patron
      4 years ago

      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's avatar
        Fowmy
        Super User
        4 years ago

        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.