Adjust data representation of a table

[Hennadii]

Hello everyone!

I'd like to create new table (Sorted Journal) using a data from existing tables (Tasks  , Journal) and "slightly" update it.

Sorted Journal - should be a table object as I'd like to create relationships in my data model with this table.

Here below are Existing and Expected Tables:

Tasks table (existing)

Task ID		Created On (m/d/y)
1		6/1/2020
2		6/2/2020

Journal table (existing)

Task ID		Date (m/d/y)		Old Status		New Status
1		6/2/2020 10:00		1		24
1		6/3/2020 14:00		24		10
1		6/3/2020 16:00		10		5
1		6/3/2020 18:00		5		10
2		6/3/2020 11:00		1		10
2		6/4/2020 14:00		10		3

Sorted Journal table (expected)

Task ID		Start Date		End Date		Initial State
1		6/1/2020		6/3/2020 14:00		Created
1		6/3/2020 16:00		6/3/2020 18:00		5
2		6/2/2020		6/3/2020 11:00		Created
2		6/4/2020 14:00				3

The idea of a new Sorted Journal table is to show a lifetime (period) of each Task. Status = 10 can be considered as a lifetime (period) splitter into separate periods.

Note: status changes neither ascending nor descending order. Status can be changed in this sequence 1-5-2-23-8-12-10 and the next lifetime (period) is 10-15-2-7-10.

Key things:

• The first record of each Task in the Journal table has always previous Old Status = 1
• A lifetime (period) of a Task is considered as Closed/Ended when a New Status = 10
• The task has new lifetime (period) when it change status from 10 (Old Status = 10)

How the Sorted Journal table should be filled?

First lifetime (period) record of each Task:

• Start Date - is a Task Created On date (from Tasks table)
• End Date - is a date when a status changed to 10 (Old Status = 10 in Journal table). In case there is no changes to status 10, the End Date should be 'blank'
• Initial State - should be set to "Created".

For the following lifetime records (if exist for a Task):

• Star Date - date when Status changed from 10 to any other
• End Date - is a date when a status changed to 10 (Old Status = 10 in Journal table). In case there is no changes to status 10, the End Date should be 'blank'
• Initial State - is equal to New Status value when it is changed from 10.

Looking forward for any advice to solve this issue.

[Anonymous]

Hi  @Hennadii ,

First create 2 calculated columns as below:

Start date = LOOKUPVALUE('Tasks table'[Created On (m/d/y)],'Tasks table'[Task ID],'Journal table'[Task ID],blank())

_Startdate = 
var _prestatus=CALCULATE(MAX('Journal table'[New Status]),FILTER(ALL('Journal table'),'Journal table'[Index]<EARLIER('Journal table'[Index])))
var maxdate=MAXX(FILTER(ALL('Journal table'),'Journal table'[Task ID]=EARLIER('Journal table'[Task ID])&&'Journal table'[Index]<EARLIER('Journal table'[Index])&&'Journal table'[Old Status]=10),'Journal table'[Date (m/d/y)])
Return
IF('Journal table'[Index]=MINX(FILTER('Journal table','Journal table'[Task ID]=EARLIER('Journal table'[Task ID])),'Journal table'[Index])||_prestatus<>10,'Journal table'[Start date],
IF('Journal table'[Old Status]=10,'Journal table'[Date (m/d/y)],maxdate))

Then create 2 measures as below:

_Enddate = 
var _preoldstatus= CALCULATE(MAX('Journal table'[Old Status]),FILTER(ALL('Journal table'),'Journal table'[Index]=MAX('Journal table'[Index])-1))
var _nexoldstatus=CALCULATE(MAX('Journal table'[Old Status]),FILTER(ALL('Journal table'),'Journal table'[Index]=MAX('Journal table'[Index])+1))
Return
CALCULATE(MAX('Journal table'[Date (m/d/y)]),FILTER('Journal table','Journal table'[New Status]=10&&_preoldstatus=10||'Journal table'[New Status]=10&&_nexoldstatus=10&&'Journal table'[Task ID]=MAX('Journal table'[Task ID])))

Initial State = 
IF(MAX('Journal table'[_Startdate])=MAX('Journal table'[Start date]),"Created",IF([_Enddate]=BLANK(),MAX('Journal table'[New Status]),CALCULATE(MAX('Journal table'[New Status]),FILTER(ALL('Journal table'),'Journal table'[Date (m/d/y)]='Journal table'[_Startdate]))))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Hennadii]

Thanks a lot for so hard work @Anonymous  !!!

It does not show me correcr result in real data I have, and it is probably I miss something while editing name of columns/tables. I'll look throug deepliy to understad how it works. The fact that it works in a sample data is awesome!

Just wondering, how many time you spent to solwe it?

[Anonymous]

Hi  @Hennadii ,

What error are you getting?Can you show me a screenshot of your error?I have created an index column to achieve the calculation,have you noticed it?

Frankly speaking,it really costs me much time achieving it.So let us work together to solve this issue.😊

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Hennadii]

Hi @Anonymous ,

I see you are using MAX to get the lates state, considering ascending order of state changes. I appologize, I did not mention in the initial post, that status is not changed in ascending order, more over, status can be even more than 10.

In my case, a status can change in this way 1-5-2-23-8-12-10 and the next llifetime (period) is starting from 10-15-2-7-10.

Just only three constant things here:

• The first record of the Task in the Journal table has always previous Old Status = 1
• A lifetime (period) of a Task is considered as Closed/Ended when a New Status = 10
• The task has new lifetime (period) when it change status from 10 (Old Status = 10)

I'll update initial post with these notes.

[Anonymous · Accepted solution]

Hi  @Hennadii ,

Sorry for the late reply.

I have corrected my .pbix file according to your extra details,see below:

Create 3 calculated columns as below:

Group = RANKX(FILTER('Journal table','Journal table'[Task ID]=EARLIER('Journal table'[Task ID])&&'Journal table'[New Status]=10),[Date (m/d/y)],,ASC)

Start date = 
IF('Journal table'[Group]=1,'Journal table'[Create date],MINX(FILTER('Journal table','Journal table'[Task ID]=EARLIER('Journal table'[Task ID])&&'Journal table'[Group]=EARLIER('Journal table'[Group])),'Journal table'[Date (m/d/y)]))

End date = 
var _maxdate=MAXX(FILTER('Journal table','Journal table'[Task ID]=EARLIER('Journal table'[Task ID])&&'Journal table'[Group]=EARLIER('Journal table'[Group])),'Journal table'[Date (m/d/y)])
Return
IF('Journal table'[New Status]=10,'Journal table'[Date (m/d/y)],IF(_maxdate='Journal table'[Start date],BLANK(),_maxdate))

As for "Initial status",there are 2 types of data,so you'd better create  a measure instead of column( 2 types are not supported in calculated column):

Initial State = 
var _max=CALCULATE(MAX('Journal table'[New Status]),FILTER(ALL('Journal table'),'Journal table'[Old Status]=10&&'Journal table'[Group]=MAX('Journal table'[Group])&&'Journal table'[Task ID]=MAX('Journal table'[Task ID])))
Return
IF(MAX('Journal table'[Group])=1,"Created",_max)

But if you wanna create a relationship using this field,you can create a calculated column as below:(you need to change the format of the value to text as shown below);

Initial status column = 
var _max=CALCULATE(MAX('Journal table'[New Status]),FILTER('Journal table','Journal table'[Old Status]=10&&'Journal table'[Group]=EARLIER('Journal table'[Group])&&'Journal table'[Task ID]=EARLIER('Journal table'[Task ID])))
Return
IF('Journal table'[Group]=1,"Created",FORMAT(_max,"general number"))

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Hennadii]

Hello @Anonymous , may I ask you for a favor?

There is another case in my data, which is not handled with current solution.

I have some Tasks (in Tasks table) which never changed their status (no records in Journal table). I'd like to list those Task in the resulting table and indicate their Start Date (same as Created On) and End Date is blank.

Could you please help to update expressions to build the resulting table?

Tasks table (existing)

Task ID		Created On (m/d/y)
...		...
3		6/3/2020

Sorted Journal table (expected)

Task ID		Start Date		End Date		Initial State
...		...		...		...
3		6/3/2020				Created

[Hennadii]

@Anonymous  AWESOME!!! IT WORKS!!!

You cannot image how much I appreciate all your efforts in solving that. I'd give you a million of kudos!

Thank you and wish you all the best!

[Hennadii]

Hi @Anonymous ,

I found one case when it does not work. Here is .pbix file and a screenshot which illustrates the issue.

I thought to use the resulting table as a table (not as table in visual) . I'd like to create some relations with other tables in my model.

Is it possible to "combine" the result into table object?

[Hennadii]

Hi @Anonymous ,

I do see only End Date for two Tasks, and the rest 500 (approx) have empty End Date (it should not be so). I'll try to navigate to a reason of that and if possible, I'll reproduce that case in sample data and send to you back for investigation.

Thank you for your afforts about that.