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" )
123abc's avatar
123abc
Community Champion
2 years ago

I apologize for the confusion. It seems I misunderstood the structure of your data and the requirement. Let's make sure we correctly filter for the "Current Week" and address the issue with the count. Please try the modified measure below:

 

DAXCopy code
Elstree 2Week Days Off = VAR ThisDay = FORMAT(TODAY(), "ddd") RETURN CALCULATE( COUNTAX( 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" ) ) )
 

This measure ensures that the filter for "Current Week" is correctly applied to the entire table before checking the "off" status for the specified day. The COUNTAX function will then count the number of rows that meet both conditions.

Make sure to replace "Elstree2weekShifts" with the actual name of your table if it's different. If the issue persists, there might be other factors affecting the result, and I would recommend checking the data in your table for any unexpected values or conditions that might be influencing the count.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

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" )