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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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