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
)

     



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