Forum Discussion

grggmrtn's avatar
grggmrtn
Post Patron
4 years ago
Solved

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

     



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.




      • grggmrtn's avatar
        grggmrtn
        Post Patron
        4 years ago

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