Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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! 🤠
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
68 | |
60 | |
51 | |
36 | |
35 |
User | Count |
---|---|
84 | |
71 | |
56 | |
45 | |
43 |