Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

miwi_0-1646742202043.png

 

Target:

miwi_0-1646745431393.png


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
11 REPLIES 11
MFelix
Super User
Super User

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

)

 

 

MFelix_0-1646733057371.png

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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...  

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?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

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

Anonymous
Not applicable

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

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_0-1646756338583.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks for this answer. It is fine to idé SequenceNo as long as the solution take into consideration that the values may change. I added a support column which I thought could help to understand ☺️

Hi @Anonymous ,

 

When you refer that the SequenceNo can change do you mean that it will change but you want the sorting to be the same? so in this case if for example EBIT changes from 0 to 55 you would still want it to keep it on the order 11?

 

Or does it mean a different thing? 

 

This questions come from the fact that to make a measure to calculate this order we need to have the logic similar. Altough I'm looking at the example you gave the only logic I see is only the sequence number however you say this can change, and on the rest of the values I do not see any logic of order being it alphabetic or number.

 

Once again sorry for all the questions but want to give you a solution that is scalabble and flexible.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

The sequenceNo may change and then the sorting should reflect that change.

When you reflect that should reflect is to keep it on the same sorting ?

 

Again EBIT row that has a sequence of 0 if it changes to 55 should keep the sorting order of 11 correct?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks for not giving up on me 🙂
If the SequenceNo of  row "EBIT" changes from 0 to 55, The sorter order will change from 11 to 12 for tat particular row.
Because: The row "EBIT margin" has the sequenceno 38, which is less than 55 so that particular row the will get sort order 11 instead of 12. 

Hope it is not impossible to grasp 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.