Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
Solved! Go to Solution.
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 )
May be this column
DealCount = IF ( Referrals[Direction] = "Out", CALCULATE ( COUNTROWS ( Referrals ), FILTER ( ALLEXCEPT ( Referrals, Referrals[DimDealNaturalID] ), Referrals[Direction] = "Out" ) ), 0 )
@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.
May be you are adding a MEASURE
I gave you a Column formula
@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?
Try this MEASURE
DealCount = IF ( SELECTEDVALUE ( Referrals[Direction] ) = "Out", CALCULATE ( COUNTROWS ( Referrals ), FILTER ( ALLEXCEPT ( Referrals, Referrals[DimDealNaturalID] ), Referrals[Direction] = "Out" ) ), 0 )
@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?
Try Replacing SelectedValue with
VALUES or
MIN or
MAX
@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 )
@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!
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 )
@Zubair_Muhammad Thank you! I was able to get it to work finally! Thanks again for your help!
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |