Forum Discussion

joerykeizer's avatar
joerykeizer
Icon for Helper II rankHelper II
9 years ago

Countifs in DAX

Hello,   I'm trying to convert a formula from excel to Power BI. In Excel: =COUNTIFS(A:A;A2;B:B;">"&B2)   The second range and ">B2" part are giving me some trouble. Your help would be very muc...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi joerykeizer,

 

I cannot dowload the sample data from the above link, so I assumed some data by myself in Excel.

 

In PowerBI, if the data table still looks like this, fisrt add a calculated column, then create a measure to count that new column:

Column3 = IF(CountIfs[Column1]=10 && CountIfs[Column2]>6,1,0)

Measure = CALCULATE(SUM(CountIfs[Column3]),ALL(CountIfs))

 

In my test, because I do not know what is the meaning of the value A2 and B2, maybe a specific value or a calculated value, I directly used 10 and 6 in formula. You should change it based on your source data. 

 

If I have something misunderstood, please share some sample data and your expected result, you can post the screenshot like the image above.

 

Thanks,
Yuliana Gu

  • joerykeizer's avatar
    joerykeizer
    Icon for Helper II rankHelper II
    9 years ago

    Thank you for your reply v-yulgu-msft

    The link still works for me but I've uploaded it somewhere else as well -> New sample data link

     

    Anyway so my expected result is this:

     

    In your example A2/B2 should have actually been A1/B1. A1 is a categorical value and B1 is a measure.

    Hopefully this will help you understand my question better.

     

    • richbenmintz's avatar
      richbenmintz
      Icon for Resident Rockstar rankResident Rockstar
      9 years ago

      countif in dax would be foo=calculate(countrows('table'), 'table'[bar] > var)

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi joerykeizer,

       

      From the pbix file you uploaded, I found that the measure RankX has returned your expected result. So, do you have any other question?

      RankX = rankx(ALLEXCEPT('Table','Table'[Store]),[SumSales])

       

      Thanks,
      Yuliana Gu

      • joerykeizer's avatar
        joerykeizer
        Icon for Helper II rankHelper II
        9 years ago

        v-yulgu-msft 

         

        I know the RankX works but I have a table with ~30K records and using it I keep getting memory errors, even with 32GB of RAM. It's just too heavy to run on large datasets. That's why I am still looking for a solution, RankX is not really an option for this.

         

        v-haibl-msft sorry to bring you into this but you help me greatly before. Do you know if the "=Countifs()" from Excel can be replicated in DAX without using RankX? I've uploaded sample data in an earlier post.