Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Transform table using DAX

Hello!

I am very happy to get ideas on how to transform a table from source to target below using DAX.
-------------
EDIT
Logic for Sum levels:
Number of sum levels are 3 since number of unique rows for ParentRowId that has a value is 3 

Sum level 3: Rows ChildRowId PTR1000721 and PTR1000725 belongs to sum level 3 since they lack ParentRowId
Sum level 2: Rows PTR1000683, PTR1000722, PTR1000723, PTR1000696 and PTR1000724 belongs to sum level 2 since their ParentRowId belongs to sum level 3
Sum level 1: Rows PTR1000684, PTR1000689, PTR1000697, PTR1000706 and PTR1000726 belongs to sum level 1 since their ParentRowId belongs to sum level 2

Logic for Sort order:
I added Support column in order to try to show the logic for Sort order. It is based on a combinaiton of Row Id and Sum level.

Source:

 

Target:


Source in Table format

PlanTemplateRowIdPlanTemplateParentRowIdPlanTemplateRowTextSequenceNo
PTR1000721 EBIT0
PTR1000683PTR1000721Gross profit1
PTR1000684PTR1000683Net sales2
PTR1000689PTR1000683Cost of sales7
PTR1000722PTR1000721Gross margin12
PTR1000723PTR1000721 13
PTR1000696PTR1000721Operating expenses14
PTR1000697PTR1000696Personnel expenses15
PTR1000706PTR1000696Other expenses24
PTR1000726PTR1000696Depreciation and amortization34
PTR1000724PTR1000721 37
PTR1000725 EBIT margin38

 

Target in table format:

ChildRowIdParentRowIdRowTextSequenceNoSum levelTextSort orderSupport column
PTR1000684PTR1000683Net sales21      Net sales100.01.02
PTR1000689PTR1000683Cost of sales71      Cost of sales200.01.07
PTR1000683PTR1000721Gross profit12   Gross profit300.01.ZZ
PTR1000722PTR1000721Gross margin122   Gross margin400.12.ZZ
PTR1000723PTR1000721 132    500.13.ZZ
PTR1000697PTR1000696Personnel expenses151      Personnel expenses600.14.15
PTR1000706PTR1000696Other expenses241      Other expenses700.14.24
PTR1000726PTR1000696Depreciation and amortization341      Depreciation and amortization800.14.34
PTR1000696PTR1000721Operating expenses142   Operating expenses900.14.ZZ
PTR1000724PTR1000721 372    1000.37.ZZ
PTR1000721 EBIT03EBIT1100.ZZ.ZZ
PTR1000725 EBIT margin383EBIT margin1238.ZZ.ZZ
Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , not able to get the logic, can you explain with example

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      MFelix  amitchandak  Thanks for taking your time... I tried to provide examples and explanations above

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi Anonymous ,

         

        I'm already abble to calculate the values on the Sum level based on the parent and child relation:

         

         

         

        Sum Level =
VAR PathValue =
    PATH ( 'Table'[PlanTemplaRowID], 'Table'[PlanTemplateParentRowID] )
VAR Pathlengthvalue =
    PATHLENGTH ( PathValue )
RETURN
    SWITCH ( Pathlengthvalue, 1, 3, 2, 2, 3, 1 )

         

         

         

        Only question that is remaining is the sorting, can it be done based on the seuquence number or not?Is the Sequence Nº is not a part of your original table? Asking this because on the first calculations I have send you refered that they could not be used.

         

         

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi Anonymous ,

     

    Add the following calculated columns:

     

    Sum level = SWITCH( 'Table'[SequnceNo],
0, 3,
1, 2,
2, 1,
7, 1,
12, 2,
13, 2,
15, 1,
24, 1,
34, 1,
14, 2,
37,2,
38,3

)
    Text = SWITCH( 'Table'[SequnceNo],
0,  "EBIT",
1, "Gross Profit",
2, "Net Sales",
7, "Cost of Sales",
12, "Gross Margin",
13, "",
15, "Personnel expenses",
24, "Other Expenses",
34, "Depreciation and amortization",
14, "Operating expenses",
37,"",
38,"EBIT Margin"

)
    Sorting = SWITCH( 'Table'[SequnceNo],
2, 1,
7, 2,
1, 3,
12, 4,
13, 5,
15, 6,
24, 7,
34, 8,
14, 9,
37, 10,
0,11,
38,12

)

     

     

    Added the sorting in order to get the correct presentation on he visuals, sort the text column by the sorting.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks MFelix... I just editeed to question above... I need to base the sum levels on the relation between Child and Parent row Ids since it may change...  

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi Anonymous ,

         

        How do you now the sorting order of the columns based on the parent child? there is an option to create a PATH function that would give you the values and each of the levels you need however when you have several values concurring at the same level how do you know the sorting?