cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

PowerBI Equivalent of Countifs when referencing same-table cell

Hey there,

 

I'm trying to create a calculated column on a date table based on the values of another table. The secondary table has dates, along with a status "Open" or "Closed". I want to graph this to show a running count of how many are open/closed by day.

 

In Excel, I accomplish this by creating a table with dates on the left side, and an equation that checks to see if the left date is <= the date in the main data table and is the status open, if so, count it.

 

In PowerBI I've created my date table, and I've been researching for a few hours and have come across using the calculate formula to replicate countifs along with a measure, but I can't seem to find out how to get the calculated formula to reference the left date to check if the date in my secondary table is <= the left-most date.

 

Any ideas would be greatly appreciated. Thanks!

3 REPLIES 3
Highlighted
Resolver II
Resolver II

PowerBI Equivalent of Countif with Criteria being <= Date on Row

Hey there,

 

I'm trying to create a calculated column on a date table based on the values of another table. The secondary table has dates, along with a status "Open" or "Closed". I want to graph this to show a running count of how many are open/closed by day.

 

In Excel, I accomplish this by creating a table with dates on the left side, and an equation that checks to see if the left date is <= the date in the main data table and is the status open, if so, count it.

 

In PowerBI I've created my date table, and I've been researching for a few hours and have come across using the calculate formula to replicate countifs along with a measure, but I can't seem to find out how to get the calculated formula to reference the left date to check if the date in my secondary table is <= the left-most date.

 

Any ideas would be greatly appreciated. Thanks!

Highlighted
Memorable Member
Memorable Member

Re: PowerBI Equivalent of Countifs when referencing same-table cell

Take a look at this video from the @AlbertoFerrari of SQLBI whilst discussing optimisation of dax he shows an example of an Open Orders calculation which sounds similar to your problem.

 

The whole video is worth watching but the open orders bit starts around 49mins.

https://www.sqlbi.com/tv/dax-optimization-examples/

 

There are a number of posts on the forum about countif which you can do using CALCULATE count and filters.

Highlighted
Super User III
Super User III

Re: PowerBI Equivalent of Countifs when referencing same-table cell

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (849)