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 filled with "open" or "Closed". what i want to achieve is a table with 3 columns 1) date 2)count of rows with state "closed" and "Data_Risol" equal to the date in field 1 3)count of rows with state either "open" or "closed" and "Data_ins" equal to the date in field 1 my problem is I know how to count such values separately but what I want to achieve is having those values compared with respect to the same date in the same table. All data is imported in direct query and has to be used in power BI report server with automated refresh. I'd be glad if someone could point me in the right direction. Thanks 🙂
  • 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)

5 Replies

  • 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)
  • razmochaev's avatar
    razmochaev
    Helper I
    6 years ago

    Hi Ste_For94 

    Unfortunately, the information you provided is not enough to provide help.

     

    1. Do you need those 3 columns in the same fact table or you want to build a virtual one in Power BI?

    2. Do you have a separate calendar table and if you do, what relationship is the active one: Data_Ins or Data_Risol?

     

    It would be best if you provide a sample table with desired output.

     

    But still you can adopt the following pattern:

    VAR MaxDate =
    MAX ( 'Calendar'[Date] ) -- saves the last visible date
VAR DesiredTable =
    ADDCOLUMNS (
        VALUES ( 'Calendar'[Date] ),
        "Open Cases", CALCULATE (
            COUNTROWS ( 'FactTable' ),
            FILTER ( VALUES ( 'FactTable'[State] ), 'FactTable'[State] = "Open" ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MaxDate )
        )
    )
RETURN
    DesiredTable

    This code returns a table, so you can use it in PowerBI