Forum Discussion

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago
Solved

Adjust data representation of a table

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Hennadii
      Icon for Helper IV rankHelper IV
      6 years ago

      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's avatar
      Hennadii
      Icon for Helper IV rankHelper IV
      6 years ago

      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