Forum Discussion

walkery's avatar
walkery
Helper I
8 years ago
Solved

CountIf PowerBI

Hello all. I'm new to PowerBI, previously only used Tableau, and have been given a dataset with ~100K records. I'm trying to get the count of the number of instances a deal number comes up where the field in my dataset Direction = "Out". In Excel I'd accomplish this using =COUNTIFS, but unfortunately I haven't be able to find a comparable PowerBI solution. I've tried the following solution based on another post I found on the forum, but it's giving me inaccurate totals. 

DealCount = CALCULATE(COUNTROWS(Referrals),ALLSELECTED(Referrals),VALUES(Referrals[DimDealNaturalID]), FILTER(Referrals, Referrals[Direction]="Out"))

 

My dataset is structured as following with the deal count field being what I'd like to calculate: 

DimDealNaturalID      Direction      DealCount

12345                          Out              2  

12345                          Out              2

12345                          In                 0

56789                          Out              1

57788                          Out              1

 

Any suggestions would be appreciated. Thanks! 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    walkery

     

    May be

     

    Please see attached file with all these formulas

     

    DealCount3 = 
IF (
   FIRSTNONBLANK(  Referrals[Direction],1 ) = "Out",
    COUNTROWS (
        FILTER (
            ALL ( Referrals ),
            Referrals[DimDealNaturalID] = VALUES ( Referrals[DimDealNaturalID] )
                && Referrals[Direction] = "Out"
        )
    ),
    0
)

12 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    walkery

     

    May be this column

     

    DealCount =
IF (
    Referrals[Direction] = "Out",
    CALCULATE (
        COUNTROWS ( Referrals ),
        FILTER (
            ALLEXCEPT ( Referrals, Referrals[DimDealNaturalID] ),
            Referrals[Direction] = "Out"
        )
    ),
    0
)
    • walkery's avatar
      walkery
      Helper I
      8 years ago

      Zubair_Muhammad thanks for the quick reply! For some reason it isn't accepting the direction field as an apporporiate field in that spot in the formula. Any idea why? It is definitely a field wtihin the Referrals table.