Forum Discussion
Countax giving wrong total
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")RETURNCALCULATE(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" )
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:
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.
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
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 codeElstree 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.
Sorry, I'm getting the following error now:
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")RETURNCALCULATE(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" )