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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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