Forum Discussion
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 ...
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.
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?