Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
walkery
Helper I
Helper I

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! 

1 ACCEPTED SOLUTION

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

Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

@walkery

 

May be this column

 

DealCount =
IF (
    Referrals[Direction] = "Out",
    CALCULATE (
        COUNTROWS ( Referrals ),
        FILTER (
            ALLEXCEPT ( Referrals, Referrals[DimDealNaturalID] ),
            Referrals[Direction] = "Out"
        )
    ),
    0
)

Regards
Zubair

Please try my custom visuals

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

Capture.PNG

@walkery

 

May be you are adding a MEASURE

 

I gave you a Column formula


Regards
Zubair

Please try my custom visuals

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

@walkery

 

Try this MEASURE

 

DealCount =
IF (
    SELECTEDVALUE ( Referrals[Direction] ) = "Out",
    CALCULATE (
        COUNTROWS ( Referrals ),
        FILTER (
            ALLEXCEPT ( Referrals, Referrals[DimDealNaturalID] ),
            Referrals[Direction] = "Out"
        )
    ),
    0
)

Regards
Zubair

Please try my custom visuals

@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? Capture.PNG

@walkery

 

Try Replacing SelectedValue with

 

VALUES or

MIN or

MAX

 

 


Regards
Zubair

Please try my custom visuals

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

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
)

Regards
Zubair

Please try my custom visuals

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

Capture.PNG

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

Regards
Zubair

Please try my custom visuals

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.