Countax giving wrong total

[PaulR17]

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 number of rows that are marked "Current Week" and not just the ones showing "off" on the selected day.

Elstree 2Week Days Off =

VAR ThisDay = FORMAT( TODAY(), "ddd" )

VAR ColumnDay = IF( ThisDay = "Mon", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Mon] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                    IF( ThisDay = "Tue", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Tue] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                        IF( ThisDay = "Wed", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Wed] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                           IF( ThisDay = "Thu", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Thu] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                             IF( ThisDay = "Fri", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Fri] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                                IF( ThisDay = "Sat", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Sat] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" ),

                                    IF( ThisDay = "Sun", CALCULATE( COUNTAX( Elstree2weekShifts, Elstree2weekShifts[Sun] = "off" ), Elstree2weekShifts[Current Week] = "Current Week" )

                                    )))))))

RETURN

ColumnDay

Any help would be greatly appreciated.

Thank you

[123abc]

It looks like you are trying to create a measure to calculate the number of "off" days for the current week based on the selected day. The issue with your measure is that you are using the COUNTAX function on the entire table, which counts all rows where the condition is true for the entire week, not just for the selected day.

To fix this, you should use a different approach. You can use the SWITCH function to simplify the nested IF statements and then calculate the count for the selected day. Here's an example of how you can modify your measure:

DAXCopy code

Elstree 2Week Days Off = VAR ThisDay = FORMAT(TODAY(), "ddd") VAR OffCount = SWITCH ( ThisDay, "Mon", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Mon] = "off")), "Tue", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Tue] = "off")), "Wed", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Wed] = "off")), "Thu", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Thu] = "off")), "Fri", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Fri] = "off")), "Sat", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Sat] = "off")), "Sun", CALCULATE(COUNTAX(Elstree2weekShifts, Elstree2weekShifts[Sun] = "off")), 0 ) RETURN OffCount

This code uses the SWITCH function to determine the day of the week and then calculates the count of "off" days for that specific day. Make sure to replace the day column names with the actual column names from your Elstree2weekShifts table.

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]

Dear @123abc thank you for this, but it still doesn't work. It returns 47 for today - Thu - in the current week, whereas it should be 3. I have no idea where the 47 comes from. Also, it doesn't look like your code is filtering for "Current Week", or am I missinig something. 

My original code returns 16, which is the total number of rows containing "Current Week", if that helps?

Thanks again for any more light that you can shed on this

[123abc]

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]

Sorry, I'm getting the following error now:

[PaulR17 · Accepted solution]

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