cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EH-IBS Frequent Visitor
Frequent Visitor

COUNTIFS WITH CONDITIONS USING VARIABLES

Hi all,

 

I'm trying to find the equivalent to a COUNTIFS excel formula in Power BI. I've already looked up related posts and they talk about using the following DAX formulas: CALCULATE, FILTER, SUMMARIZE, etc but I just can't find the way to make my COUNTIFS to work. I think the difference with other posts is that instead of using constant values as criteria, I'm using variables, which I think complicates things (or perhaps not?).

 

As you can see in the screenshot below, I have two tables: the "AIRCRAFT" table which has a unique row for each aircraft and the dates when they started and ended operations, and the "ACTIVE AIRCRAFT" table, which basically has a DATE column and counts the number of aircraft which have been active each day. The excel formula can be seen below (sorry because it's in Spanish). What would the equivalent be in DAX measure? (supposing the tables and columns have the same name). I have tried using CALCULATE and FILTER in a DAX measure but I can't relate other tables' columns in the measure. Columns G, H and I are just for troubleshooting reference (they're not part of the table).

 

I'm very new to Power BI and DAX but I just can't find the solution to this in the PBI Forums. Thanks in advance guys!

 

=COUNTIFS($B$3:$B$5;"<="&$E3;$C$3:$C$5;">="&$E3)

imagen.png 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Hi @EH-IBS ,

 

The corresponding IFS on COUNTIFS on dax is made making use of the CALCULATE function alonside with the filters on that function.

 

You need to create a measure similar to this:

Countif Aircraft =
VAR Selected_date =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNT ( Aircraft[Aircraft] );
        Selected_date >= Aircraft[Start];
        Selected_date <= Aircraft[End]
    )

Change the table names to your model be aware that in this formula the two tables are not related.

 

See attach image and PBIX file for explanation:

Count_if.png

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
Super User
Super User

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Hi @EH-IBS ,

 

The corresponding IFS on COUNTIFS on dax is made making use of the CALCULATE function alonside with the filters on that function.

 

You need to create a measure similar to this:

Countif Aircraft =
VAR Selected_date =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNT ( Aircraft[Aircraft] );
        Selected_date >= Aircraft[Start];
        Selected_date <= Aircraft[End]
    )

Change the table names to your model be aware that in this formula the two tables are not related.

 

See attach image and PBIX file for explanation:

Count_if.png

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

EH-IBS Frequent Visitor
Frequent Visitor

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Thank you very much @MFelix for your fast reply! Smiley Wink

 

The key to success was the VAR. I had NEVER seen this coming up in any post. Very useful. I will now use this with other tables where I have the same problem.

Super User
Super User

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Hi @EH-IBS,

The variable is just a way to reuse code. The trick is the formula of the variable in the case the MAX function that give context for picking up the date from your table visual and then comparing it with the start date and end date.

In DAX you need to pay attention to your context given by the slicers, the columns on the visuals, the filters and even the parameters on the formulas itself.

Regards
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




EH-IBS Frequent Visitor
Frequent Visitor

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Hi @MFelix,

 

I'm having problems with this solution.

 

If I use this code as a measure, it works fine until there is a date where an aircraft starts or stops operating. I think this has to do with the MIN or MAX aggregate function you have introduced in the code. It gives the correct number for each row but not for the total. I want the total to be the SUM of all the individual row values.

 

If I use this code as a column, it works fine even without the MIN or MAX aggregate function, and returns the correct values for the row and the total. However, as soon as I create another calculated column, it gives me a "circular dependency" error which I can't understand. I have read some posts on this but still can't understand how to get around it in my case.

 

What I am trying to do can't be that complicated. It's really frustrating!

 

Regards!

Super User
Super User

Re: COUNTIFS WITH CONDITIONS USING VARIABLES

Hi @EH-IBS ,

 

On the first post didn't realize you needed to summarize the total values, in this case since measure are based on context you need to use an aggregator SUMX to make the calculation of the total row in this case you need to create and additional measure (this is to make it easier to make change in the future:

 

totalCount = SUMX('Calendar';[Countif Aircraft])

Then use this as the measure on your table.

 

Check PBIX file attach.

 

Regards,

MFelix

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,238 guests
Please welcome our newest community members: