Forum Discussion

Ste_For94's avatar
Ste_For94
Frequent Visitor
6 years ago
Solved

Counting rows aggregating on different columns

Hi everyone i'm still a beginner in DAX and I'm trying to solve the following problem. I have a table with two date fields: "Data_Ins" and "Data_Risol". also i have a "state" field that can be either ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Ste_For94 ,

     

     

     

    Use the below measures.

     

    Count of Closed Tickets = CALCULATE(COUNTROWS(Incidents),FILTER(ALL(Incidents), Incidents[ClosedDate] = MAX('Calendar'[Date]) && Incidents[Status] = "Closed"))
     
     
    Count of Open Tickets = CALCULATE(COUNTROWS(Incidents),FILTER(ALL(Incidents), Incidents[OpenDate] = MAX('Calendar'[Date])))
     
     
    Regards,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Ste_For94 ,

 

 

 

Use the below measures.

 

Count of Closed Tickets = CALCULATE(COUNTROWS(Incidents),FILTER(ALL(Incidents), Incidents[ClosedDate] = MAX('Calendar'[Date]) && Incidents[Status] = "Closed"))
 
 
Count of Open Tickets = CALCULATE(COUNTROWS(Incidents),FILTER(ALL(Incidents), Incidents[OpenDate] = MAX('Calendar'[Date])))
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
  • Ste_For94's avatar
    Ste_For94
    Frequent Visitor
    6 years ago

    Thanks Anonymous I think this should work! still I'm having problems in implementing what you did and all I'm getting is an empty table... did you link somehow the calendar table to the incident table? if so how? (join, relation...)

    also where should i build my measures, into the incident or the calendar table?

    thank you, sorry to bother 🙂

     

    Stefano

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ste_For94 ,

       

      Created a Calendar Table.

      Calendar = CALENDARAUTO()
       
       
       
       
      Right Click on Incident Table and create New Measures
       
      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
      • Ste_For94's avatar
        Ste_For94
        Frequent Visitor
        6 years ago

        yep done that already, I made a mistake in selecting the filter field...

        and then the numbers were not adding up because if say i select only years and months it does not work unless i modify the measure like this:

        Risolta = CALCULATE(COUNTROWS('Criticità_Mare_MT');FILTER(ALL('Criticità_Mare_MT');
        MONTH('Criticità_Mare_MT'[DATA_RISOLUZIONE])=MONTH(MAX('Calendar'[Date]))&&
        YEAR('Criticità_Mare_MT'[DATA_RISOLUZIONE])=YEAR(MAX('Calendar'[Date]))
        &&'Criticità_Mare_MT'[STATO]="RS"&&'Criticità_Mare_MT'[VALUTAZIONE_CRIT]="1"))
         
        now it works! still at te beginning of my learning curve... thanks a lot 🙂