Forum Discussion

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
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

Not applicable

6 years ago

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)

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 🙂