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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.