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, 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 

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

5 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you very much MFelix for your fast reply! :smileywink:

       

      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'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