COUNTIFS WITH CONDITIONS USING VARIABLES

[EH-IBS]

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)

 

[MFelix · Accepted 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

[EH-IBS]

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

[MFelix]

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

[EH-IBS]

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!

[MFelix]

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