Forum Discussion

PaulR17's avatar
PaulR17
Frequent Visitor
2 years ago
Solved

Countax giving wrong total

Hi, I am going round and round in circles trying to calculate the number of "off" days for today in the cuurent week for following table:     This is my measure, but it returns the total nu...
  • PaulR17's avatar
    PaulR17
    2 years ago

    OK, problem solved. It was COUNTAX that was causing the problem. Substituting with CALCULATE and COUNTROWS gives the correct answer. 

    Thanks very much for your help, the FILTER was essential.

     

    Elstree 2Week Days Off =
    VAR ThisDay = FORMAT(TODAY(), "ddd")

    RETURN
    CALCULATE(
        COUNTROWS(
         FILTER( Elstree2weekShifts, Elstree2weekShifts[Current Week] = "Current Week" ) ),
            SWITCH( ThisDay,
                    "Mon", Elstree2weekShifts[Mon],
                    "Tue", Elstree2weekShifts[Tue],
                    "Wed", Elstree2weekShifts[Wed],
                    "Thu", Elstree2weekShifts[Thu],
                    "Fri", Elstree2weekShifts[Fri],
                    "Sat", Elstree2weekShifts[Sat],
                    "Sun", Elstree2weekShifts[Sun] ) = "off" )
PaulR17's avatar
PaulR17
Frequent Visitor
2 years ago

Sorry, I'm getting the following error now:

 

 

PaulR17's avatar
PaulR17
Frequent Visitor
2 years ago

OK, problem solved. It was COUNTAX that was causing the problem. Substituting with CALCULATE and COUNTROWS gives the correct answer. 

Thanks very much for your help, the FILTER was essential.

 

Elstree 2Week Days Off =
VAR ThisDay = FORMAT(TODAY(), "ddd")

RETURN
CALCULATE(
    COUNTROWS(
     FILTER( Elstree2weekShifts, Elstree2weekShifts[Current Week] = "Current Week" ) ),
        SWITCH( ThisDay,
                "Mon", Elstree2weekShifts[Mon],
                "Tue", Elstree2weekShifts[Tue],
                "Wed", Elstree2weekShifts[Wed],
                "Thu", Elstree2weekShifts[Thu],
                "Fri", Elstree2weekShifts[Fri],
                "Sat", Elstree2weekShifts[Sat],
                "Sun", Elstree2weekShifts[Sun] ) = "off" )