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

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      walkery

       

      Try Replacing SelectedValue with

       

      VALUES or

      MIN or

      MAX

       

       

      • walkery's avatar
        walkery
        Helper I
        8 years ago

        Zubair_Muhammad Thanks for the suggestion. I tried it with each of those suggestions and while the measure doesn't give an error, when I try to add the measure into the table it just hangs for an extended period of time and then provides a time-out error. Is there any workaround to this do you think? Or is it too memory intensive of a process and would need to be added in by the DBA on the backend?