Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

DAX - Count Row based on two criteria

Hi I am trying to count the rows of a table based on two criteria. 

 

Criteria 1 - SLA Pass = "NO" 

Criteria 2 - Date Logged >= 01/09/2020

 

The result I would exspect based on the sample table below would be equal to 4

 

I have tried this DAX measure 

Measure = CALCULATE(
COUNTROWS(Repairs Table),
RepairsTable, Repairs Table[SLA Pass] ="NO",
Repairs Table[Date Logged]>=01/09/2020)

 

This measure works for the count rows and SLA Pass = No but seems to ignore date criteria?       

Repairs Table 
Date LoggedDate CompletedTradeSLA Passed
01/08/202001/08/2020PlumbingYes
12/08/202012/08/2020PlumbingYes
11/08/202011/08/2020PlumbingYes
17/08/202018/08/2020PlumbingYes
01/09/202001/09/2020PlumbingNo
01/09/202020/09/2020PlumbingYes
07/08/202007/08/2020ElectricalNo
17/09/202017/10/2020ElectricalNo
03/09/202003/09/2020ElectricalNo
06/08/202006/09/2020ElectricalNo
25/08/202025/09/2020ElectricalNo
25/08/202025/09/2020ElectricalNo
15/08/202015/09/2020ElectricalNo
14/08/202014/09/2020ElectricalYes
14/09/202014/09/2020ElectricalYes
16/09/202016/09/2020ElectricalNo
18/08/202018/08/2020RoofingYes
21/08/202021/08/2020RoofingYes
13/08/202013/10/2020RoofingNo
29/08/202029/08/2020RoofingYes
21/08/202021/08/2020RoofingNo
07/09/202007/10/2020RoofingYes

 

thanks

Richard

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    cottrera , Try like

    Measure = CALCULATE(
    COUNTROWS(Repairs Table),
    RepairsTable, Repairs Table[SLA Pass] ="NO",
    Repairs Table[Date Logged]>= date(2020,09,01))

4 Replies

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi,

     

    I think you are passing date in "dd/mm/yyyy" format whereas powerbi is filtering in "mm/dd/yyyy".

     

    Can you pass date using Date function and try.

     

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

    • cottrera's avatar
      cottrera
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi nvprasad  thank you for responding but the suggestion did not work for me. I tried 

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    cottrera , Try like

    Measure = CALCULATE(
    COUNTROWS(Repairs Table),
    RepairsTable, Repairs Table[SLA Pass] ="NO",
    Repairs Table[Date Logged]>= date(2020,09,01))