topic Re: Manipulating Tracking Data in DAX Commands and Tips

Manipulating Tracking Data

Anonymous — Thu, 20 Feb 2020 16:20:03 GMT

I have a table like the one below:

ID Status

1 Old

1 Started

1 New

2 Started

2 New

3 New

4 Old

4 Partially Done

4 New

Basically what I want to be able to do is to see which IDs passed through the status 'Started' and are now at 'New'

So in my case, ID 1 and 2 would fit that criteria.

I can't find the right query to do this, I can only find the previous status but I need to look through all historical statuses.

Any guidance would be much appreciated.

Thanks

Victoria

Re: Manipulating Tracking Data

Pragati11 — Thu, 20 Feb 2020 17:21:46 GMT

HI Anonymous ,

As you haven't mentioned the desired output, I have created a measure as follows to identify the IDs in the data which have "New" and "Started" as their statuses:

1. GREEN highlight - This is the sample data used

2. RED highlight - This shows a table with "testCheck" measure using which it only displays the IDs with "New" and "Started" statuses

3. BLUE highlight - Measure created to achieve this.

If this helps and works please give a Kudos and mark this as a solutions! 🙂

Thanks,

Pragati

Re: Manipulating Tracking Data

Anonymous — Fri, 21 Feb 2020 03:59:28 GMT

If you want take count
Simply create measure
Measure
Var tab=summerize(table,table[id],"new", calculate(count(table[id]),filter(table,table[status]="new")),"started",calculate(count(table[id]),filter(table,table[status]="started")))
Return
Sumx(tab,if([new]>0 && [started]>0,1,0))

Thanks
Pravin