locked
DAX CALCULATE filter Order RRS feed

  • Question

  • Hello

    I understand with the Calculate function filters are evaluated from the outside in. I’ve got two functions below that to me are doing the same thin, but producing slightly different results, the main difference is that the filters in different positions?

    <o:p> </o:p>

    Can someone explain, why I’m getting a slightly result with the two functions? What is the rule on how I should filter with Calculate?  <o:p></o:p>

     <o:p></o:p>

    Correct result – Returns 38<o:p></o:p>

    EVALUATE
    ROW (
        
    "DEF"CALCULATE (
            
    COUNTAX (
                Employee_Event,
                
    IF (
                    
    Employee_Event[Event_Status] = "Pass",  1)
            ),   
    FILTER ( Employee_Module, Employee_Module [Mod_End_Date] <= [D_L_Date]&&Employee_Module[Mod_End_Date] >= [D_F_Date] )
        )
    )<o:p></o:p>

     <o:p></o:p>

    Incorrect result – Returns 37<o:p></o:p>

    EVALUATE
    ROW (
        
    "ABC"CALCULATE (
            
    COUNTAX (
                Employee_Module,
                
    IF (
                    Employee_Module
     [Mod_End_Date] <= [D_L_Date]
                        
    && Module[Mod_End_Date] >= [D_F_Date],1 )
            ),        
    FILTER (Employee_EventEmployee_Event [Event_Status] = "Pass")
        )
    )

    Thanks in advance.

    Roy

    <o:p></o:p>

    Wednesday, June 20, 2018 8:06 PM

Answers

  • Hi Roy,

    Thanks for your question.

    >>>Can someone explain, why I’m getting a slightly result with the two functions? What is the rule on how I should filter with Calculate?
    The key thing here is the COUNTAX function, not the calculate. The COUNTAX function counts nonblank results. NONBLANK is the key here. False is not the same as BLANK(). BLANK() is more like saying “I do not know” instead of a yes or a no (a true or a false) which are things you say when you do know. False’s get counted by COUNTAX, just like true’s, because neither is the same as blank.

    The count of  

    Employee_Event[Event_Status] = "Pass"  and the count of

    Employee_Module [Mod_End_Date] <= [D_L_Date]
                        
    && Module[Mod_End_Date] >= [D_F_Date] 

    should be different.

    For more information, please refer to COUNTAX Function (DAX) .


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ryand09 Thursday, June 21, 2018 8:04 AM
    Thursday, June 21, 2018 6:21 AM

All replies

  • Hi Roy,

    Thanks for your question.

    >>>Can someone explain, why I’m getting a slightly result with the two functions? What is the rule on how I should filter with Calculate?
    The key thing here is the COUNTAX function, not the calculate. The COUNTAX function counts nonblank results. NONBLANK is the key here. False is not the same as BLANK(). BLANK() is more like saying “I do not know” instead of a yes or a no (a true or a false) which are things you say when you do know. False’s get counted by COUNTAX, just like true’s, because neither is the same as blank.

    The count of  

    Employee_Event[Event_Status] = "Pass"  and the count of

    Employee_Module [Mod_End_Date] <= [D_L_Date]
                        
    && Module[Mod_End_Date] >= [D_F_Date] 

    should be different.

    For more information, please refer to COUNTAX Function (DAX) .


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ryand09 Thursday, June 21, 2018 8:04 AM
    Thursday, June 21, 2018 6:21 AM
  • Willson 

    Many thanks for the prompt reply. It makes sense now

    Roy.

    Thursday, June 21, 2018 8:04 AM