Forum Discussion

v_mark's avatar
v_mark
Helper V
2 years ago

Translate Multiple Excel Countifs into DAX

I was trying to re-create 4 measures from Excel that uses Countifs ...

 

 

WIP - Inside SLA = COUNTIFS(Export!$B:$B,"<="&RM!G$5+1,Export!$C:$C,">="&RM!G$5+1,Export!$D:$D,">"&RM!G$5+1,Export!$E:$E,IF($E6="","*",RM!$E6))+
COUNTIFS(Export!$B:$B,"<="&RM!G$5+1,Export!$C:$C,">="&RM!G$5+1,Export!$D:$D,"",Export!$E:$E,IF($E6="","*",RM!$E6))

WIP - Failed SLA = =COUNTIFS(Export!$B:$B,"<="&RM!G$5+1,Export!$C:$C,"<"&RM!G$5+1,Export!$D:$D,">"&RM!G$5+1,Export!$E:$E,IF($E6="","*",RM!$E6))
+COUNTIFS(Export!$B:$B,"<="&RM!G$5+1,Export!$C:$C,"<"&RM!G$5+1,Export!$D:$D,"",Export!$E:$E,IF($E6="","*",RM!$E6))

Closed - Pass SLA = =COUNTIFS(Export!$D:$D,"<="&RM!G$5+1,Export!$D:$D,">"&RM!F$5+1,Export!$E:$E,IF($E6="","*",RM!$E6),Export!$F:$F,"pass")

Closed - Fail SLA = =COUNTIFS(Export!$D:$D,"<="&RM!G$5+1,Export!$D:$D,">"&RM!F$5+1,Export!$E:$E,IF($E6="","*",RM!$E6),Export!$F:$F,"fail")​​​

 

 

Here are the desired results. The ones highlighted in yellow. 

I tried to build a date dimension together with my fact table and create relationship in between. 

I tried to create a couple of approaches but it looks like I am missing something in my DAX. 

The result that I get is perfect in measure called New but almost close to the other shown above vs the desired results.

Each measure has it's own criteria that goes with a specific date field inside the fact table like Submitted, Target and Close Dates. 

Do you guys have an idea what I am missing? Any idea or suggestion is highly appreciated!

For quick reference Here's the link Power BI
Sample PBIX and Excel File ( for the desired Results )

 

 

 

 

 

Need Help
Show and Tell

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Forget Excel.  Describe your business rules .  

     

    Avoid nesting measures.  For example:

     

    _Closed - Pass SLA = 
VAR _weekending = MAX('Dates'[Week Ending Date])
RETURN  COALESCE(CALCULATE(
    COUNTROWS(Test),
    'Test'[Pass/Fail] = "Pass",
    'Test'[Close Out Date] <= _weekending,
    USERELATIONSHIP(Dates[Date],Test[Close Out Date])
),0)
    • v_mark's avatar
      v_mark
      Helper V
      2 years ago

      lbendlin - I know getting a concrete understanding about the process would be valuable. 
      Just a question please. When you say "Avoid nesting Measures" What do you mean by that?. I saw you paste a measure that has a several DAX on it. Is that the one you are pertaining at? 

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        You used CALCULATE([_test Close Date],..)  which I replaced by CALCULATE(COUNTROWS(Test),...) - that makes it clearer what is happening, and avoids confusion on context transitions.