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

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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 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? 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi walkery

     

    try this one...May be

     

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

    Zubair_Muhammad It was moving faster, so I was hopeful but it resulted in an error with using the Min function. Any other workarounds? Thanks for your continued suggestions! 

  • walkery's avatar
    walkery
    Helper I
    8 years ago

    Zubair_Muhammad Thank you! I was able to get it to work finally! Thanks again for your help!