Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to Solution.
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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you very much @MFelix for your fast reply!
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |