Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hi All,
need help in count in criteria:-
<30
>=30 &<70
>=70
Sub | No. | ||||||
A | 20 | Count by criteria | |||||
A | 29 | Sub | <30 | >=30 & <70 | >=70 | ||
A | 31 | A | 2 | 5 | 3 | ||
A | 35 | B | 4 | 3 | 3 | ||
A | 55 | ||||||
A | 80 | ||||||
A | 85 | ||||||
A | 75 | ||||||
A | 45 | ||||||
A | 40 | ||||||
B | 10 | ||||||
B | 15 | ||||||
B | 17 | ||||||
B | 28 | ||||||
B | 40 | ||||||
B | 45 | ||||||
B | 56 | ||||||
B | 80 | ||||||
B | 91 | ||||||
B | 78 |
It's easy in excel but in power BI i can't figure out this.
Best regards,
NICK
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
Hi @vanessafvg
Can it possible if criteria range is not filfull condition.
it show 0 instead of blank
Sub | Count | <30 | >=30 - <70 | >=70 |
A | 10 | 5 | 5 | |
B | 10 | 5 | 3 | 2 |
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
Proud to be a Super User!
@Naveennegi119what are you filtering on?
Proud to be a Super User!
I'm using Chiclet slicer,
problem is
Selected Apple in slicer | ||||||||
ChicletSlicer | Sub | <30 | >=30 - <70 | <=70 | ||||
Apple | A | 5 | 5 | |||||
Mango | B | 5 | 3 | 2 | ||||
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 | A | 5 | 0 | 5 | ||||
Mango | B | 5 | 3 | 2 | ||||
Banana | A | 0 | 0 | 0 | ||||
B | 0 | 0 | 0 | |||||
A | 0 | 0 | 0 | |||||
B | 0 | 0 | 0 |
showing apple value with 0, that's good
but also show other data value with 0
Regards,
NICK
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
68 | |
67 |