DAX to calculate row count based on filtered table

[drstrong]

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 ) ) )

[Daniel29195 · Accepted solution]

@drstrong 

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! 🤠