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)
razmochaev's avatar
razmochaev
Icon for Helper I rankHelper 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