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
Naveennegi119
Helper III
Helper III

Count by Criteria

Hi All,

 

 

need help in count in criteria:-

 

<30

>=30 &<70

>=70

 

SubNo.      
A20  Count by criteria   
A29  Sub<30>=30 & <70>=70
A31  A253
A35  B433
A55      
A80      
A85      
A75      
A45      
A40      
B10      
B15      
B17      
B28      
B40      
B45      
B56      
B80      
B91      
B78      

 

It's easy in excel but in power BI i can't figure out this.

 

Best regards,

NICK

1 ACCEPTED SOLUTION
vanessafvg
Super User
Super User

@Naveennegi119 

 

Measure > 30 = CALCULATE(COUNTROWS(Table2),Table2[No.] < 30)

Measure 30&70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >= 30, Table2[No.] < 70)

Measure70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >=70)

 

that should give you the idea

and gives this result

 

 

 

Capture.PNG

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
vanessafvg
Super User
Super User

@Naveennegi119 

 

Measure > 30 = CALCULATE(COUNTROWS(Table2),Table2[No.] < 30)

Measure 30&70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >= 30, Table2[No.] < 70)

Measure70 = CALCULATE(COUNTROWS(Table2),Table2[No.] >=70)

 

that should give you the idea

and gives this result

 

 

 

Capture.PNG

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for sharing Idea @vanessafvg

 

Regards,

NICK

Hi @vanessafvg

 

Can it possible if criteria range is not filfull condition.

 

it show 0 instead of blank

 

SubCount<30>=30 - <70>=70
A105 5
B10532

 

See in A >=30 - <70 catagory

 

Regards,

NICK

@Naveennegi119 Measure70 = CALCULATE(COUNTROWS(Table2)+0,Table2[No.] >=70)

 

you can just add +0 

 

you can also use an if statement with isblank but adding + is easier





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg,

 

it's working but not usable with filter or slicer.

 

but thanks for help

 

 

Regards,

NICK

@Naveennegi119what are you filtering on?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg

 

I'm using Chiclet slicer,

 

problem is

 

Selected Apple in slicer      
 ChicletSlicer  Sub<30>=30 - <70<=70 
 Apple  A5 5 
 Mango  B532 
 Banana       
   Measure >30 = CALCULATE(COUNTROWS('FT Test Result'),'FT Test Result'[Percentage]<30)
         
         
         
After using this formula      
Measure >30 = CALCULATE(COUNTROWS('FT Test Result')+0,'FT Test Result'[Percentage]<30)
         
         
Selected Apple in slicer      
 ChicletSlicer  Sub<30>=30 - <70<=70 
 Apple  A505 
 Mango  B532 
 Banana  A000 
    B000 
    A000 
    B000 

 

showing apple value with 0, that's good

but also show other data value with 0

 

Regards,

NICK

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.