Count values in row in DAX

[Anonymous]

Hello,

I have weekly collected project trends data (please check in attachment) I would like to build DAX column which counts  no of weeks in last state, ideally it should start counting if state is changing. So e.g project was in Amber state for 10 weeks but it changed to green and it should start counting one more time.

Could you advise, please?

Thanks!

[v-yulgu-msft]

Hi @Anonymous ,

Does above image show the source data table? Do you want to count the number of weeks per row? How to count? Could you please show us the desired result with examples?

Best regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft,

Above are shown some dummy data I created to show how data are collected and what I want to build.
I would like to build an expression which will count for each row no of cells in a row with followinh logic:

rw - row

cl - column

n - variable
if cell(rw, cl) = cell(rw, cl+1) then n = n+1

else if cell(rw,cl) <> cell(rw, cl +1) then n = 1

Main point of it it to count no of weeks in current state, not all specific values but to start counting again if cells have different value.

[Anonymous]

hello @Anonymous,

try using VBA to solve that issue

[Anonymous]

Already did it in VBA - but thank you 🙂

I just wonder if this is possible in DAX too