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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply

Count based on condition

Hi Community,

Please review below and provide your suggestion,

I want to count organization based on “Decision”, I have used below formula and it not showing right result.

Count =
COUNTX(

    FILTER ( 'Raw Data',' Raw Data '[Decision] = "Award" ),

    ''Raw Data''[organization])

Raw Data

Organization

Decision

ABC

Award

ABC

Award

ABC

Decline

XYZ

Award

 

Final Result

Organization

Decision

Count only based on Awarded

ABC

Award

2

XYZ

Award

1

 

1 ACCEPTED SOLUTION

HI @tejapowerbi123 ,

 

Create a Calculated Column

 

Column = CALCULATE(COUNT(Table2[Decision]),FILTER( ALLEXCEPT(Table2,Table2[Organization]), Table2[Decision] = "Award"))

 

1.jpg2.JPG

 

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

 

View solution in original post

13 REPLIES 13
swise001
Continued Contributor
Continued Contributor

Try This:
 
Count = CALCULATE(COUNTROWS('Raw Data'),'Raw Data'[Decision]="Award")

@swise001  I have used your suggestion and still not getting right number.

Count only based on Awarded
1
1

I replicated your scenario and your measure displays the correct result. Below is the pbix file.

@AntrikshSharma thank you so much.I am trying to create in calculated column and i want to use column result to next formula.

If it is a calculated column then you can use this. Modify the IF statement as per your requirement.

Column =
VAR CurrentOrganization = RawData[Organization]
VAR RowsSatisfyingCondition =
    COUNTROWS (
        FILTER (
            RawData,
            RawData[Organization] = CurrentOrganization
                && RawData[Decision] = "Award"
        )
    )
VAR Result =
    IF ( RawData[Decision] = "Award", RowsSatisfyingCondition )
RETURN
    Result

 

@AntrikshSharma sorry still not getting the right result.not right.png

HI @tejapowerbi123 ,

 

Create a Calculated Column

 

Column = CALCULATE(COUNT(Table2[Decision]),FILTER( ALLEXCEPT(Table2,Table2[Organization]), Table2[Decision] = "Award"))

 

1.jpg2.JPG

 

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

 

Thank you so much one for your great suggestions.

You said you are trying to create a calculated column and what you have in the image is a measure.

@AntrikshSharma  no it is column.Thanks

pranit828
Community Champion
Community Champion

Hi @tejapowerbi123 

 

I would use

 

_Count = 

CALCULATE(COUNTX('Raw Data'[organization]), ALLEXCEPT('Raw Data','Raw Data[Decision]))

(OR)

CALCULATE(COUNTROWS('Raw Data'),'Raw Data'[Decision]="Award")

 

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hi @tejapowerbi123 

 

I believe what you are looking for is below:

 

CALCULATE (
COUNTROWS('Raw Data'),
FILTER ( ALLEXCEPT('Raw Data','Raw Data'[Decision]),'Raw Data'[Decision]="Award")
)

 

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Thank you so much Pranit, I have used your suggestion but result is still not right.

Count only based on Awarded
2411
2411

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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