cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ste_For94
Frequent Visitor

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 🙂
1 ACCEPTED SOLUTION
harshnathani
Super User
Super User

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])))
 
1.jpg2.JPG
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

5 REPLIES 5
harshnathani
Super User
Super User

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])))
 
1.jpg2.JPG
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Thanks @harshnathani 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

@Ste_For94 ,

 

Created a Calendar Table.

Calendar = CALENDARAUTO()
 
1.jpg
 
 
2.JPG
 
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)

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 🙂
 
razmochaev
Helper I
Helper I

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors