Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, SU...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago
Hi Anonymous,

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
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous ,

     

    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