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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors