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

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

walkery

 

May be you are adding a MEASURE

 

I gave you a Column formula

  • walkery's avatar
    walkery
    Helper I
    8 years ago

    Zubair_Muhammad Ah that makes sense. It doesn't look like with my data connection I'm able to add a new column, it's greyed out and only "new Measure" is avaialble to be selelected. Is there a way to accomplish what I'm looking to do with a measure? 

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      walkery

       

      Try this MEASURE

       

      DealCount =
IF (
    SELECTEDVALUE ( 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 still getting an error. Maybe I'm using a different version of PowerBI? I don't know that I have SELECTEDVALUE as a function available. Is it perhaps added with a 3rd party package? I have ALLSELECTED available. Would that accomplish the same task?