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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
drstrong
Frequent Visitor

DAX to calculate row count based on filtered table

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

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@drstrong 

output

Daniel29195_0-1708038640230.png

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

View solution in original post

1 REPLY 1
Daniel29195
Super User
Super User

@drstrong 

output

Daniel29195_0-1708038640230.png

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.