Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have ORDER table and I am needing to count rows based on month/activity/stage. Below is an example of the data and what I am trying to accomplish.
count of activity stage by month where
activity = ready & stage > 1
activity = shipped & stage > 2
activity = delivered & stage >= 3
Data:
Month Activity Stage
May Ready 1
May Shipped 2
May Shipped 2
May Delivered 3
May Delivered. 3
June Ready 1
Results:
Month Activity Stage Progress Count
May Ready 4
May Shipped 2
May Delivered 2
June Ready 1
I have tried the below but out of ideas.
COUNTROWS( FILTER( ORDER, Order[month] = EARLIER(Order[month]) && Order[stage] > EARLIER(order[stage]) ) ), ALLEXCEPT(order, order[activity]) )
GROUPBY( 'ORDER', 'ORDER'[Month], 'ORDER'[Activity], "Stage Progress Count", CALCULATE( COUNTAX( FILTER( VALUES('ORDER'), 'ORDER'[Month] = EARLIER('ORDER'[Month]) && ( ('ORDER'[Activity] = "Ready" && 'ORDER'[Stage] > 1) || ('ORDER'[Activity] = "Shipped" && 'ORDER'[Stage] > 2) || ('ORDER'[Activity] = "Delivered" && 'ORDER'[Stage] >= 3) ) ), 1 ) ) )
Solved! Go to Solution.
output
NB : base on the condition you wrote, june shouldnt display,
"" activity = ready & stage > 1 "" for june, stage is = 1 , but the condition is taking stage > 1 only .
measure :
Measure 3 =
var ds =
ADDCOLUMNS(
SUMMARIZE(
Table5,
Table5[Month],
Table5[Stage],
Table5[ Activity]
),
"@c" ,
var m = Table5[Month]
var s = Table5[Stage]
var a = Table5[ Activity]
return
SWITCH(
TRUE(),
a = "Ready" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>1
),
a = "Shipped" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>2
),
a = "Delivered" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>=3
)
)
)
RETURN
SUMX(ds,[@c])
let me know if this helps
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
output
NB : base on the condition you wrote, june shouldnt display,
"" activity = ready & stage > 1 "" for june, stage is = 1 , but the condition is taking stage > 1 only .
measure :
Measure 3 =
var ds =
ADDCOLUMNS(
SUMMARIZE(
Table5,
Table5[Month],
Table5[Stage],
Table5[ Activity]
),
"@c" ,
var m = Table5[Month]
var s = Table5[Stage]
var a = Table5[ Activity]
return
SWITCH(
TRUE(),
a = "Ready" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>1
),
a = "Shipped" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>2
),
a = "Delivered" ,
CALCULATE(
COUNTROWS(Table5),
ALLEXCEPT(Table5,Table5[Month]),
Table5[Stage]>=3
)
)
)
RETURN
SUMX(ds,[@c])
let me know if this helps
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |