topic Re: Table Transformation with DAX in DAX Commands and Tips

Table Transformation with DAX

VikrantC — Sat, 26 Mar 2022 16:39:14 GMT

I have a Table (Table1) with date, product and price columns. I calculated Monthly change as Table2 using a measure (Monthly%_Change) and created a new column called "Change".

I used the Table2 to create a new table (Table3) for columns for "productA" and "ProductB". I want to use the Table3 and I do not have any use for Table2. How do I write the DAX formula to create Table3?

Table 1 =

Date	Product	Price
1/1/2022	Product A	$5
1/1/2022	Product B	$8
2/1/2022	Product A	$10
2/1/2022	Product B	$4

Table2 =

SUMMARIZECOLUMNS(Calender[End Of Month],

Table1[[Product],

"Change", [Monthly%_Change]

Date	Product	Change
1/1/2022	Product A	0%
1/1/2022	Product B	0%
2/1/2022	Product A	100%
2/1/2022	Product B	50%

Table3 =

SUMMARIZECOLUMNS(Calender[End Of Month],

"ProductA%", CALCULATE(MIN('Table2'[Change] ), 'Table2'[Product] = "ProductA"),

"ProductB%", CALCULATE(MIN('Table2'[Change]), 'Table2'[Product] = "ProductB")))

Date	Product A%	Product B%
1/1/2022	0%	0%
2/1/2022	100%	50%

Re: Table Transformation with DAX

Whitewater100 — Sat, 26 Mar 2022 21:05:47 GMT

Hi:

Please see attached link. I included a couple extra DAX formulas that show the build and included a Date Table to optimize these types of calculations.

https://drive.google.com/file/d/1DfLQlZaYxSzRJxnjljCwPu1MxvyRzFwV/view?usp=sharing

Re: Table Transformation with DAX

VikrantC — Sat, 26 Mar 2022 21:18:20 GMT

Hi,

I am able to do this. However, I want to create a virtual Table3 so that I can use in other formulas. Currently, I am taking the Table1 and I used a measure to created another table (Table2) to crate percentage. I am transposing it in Table3. Basically, I am trying to get to Table3 without creating Table2. If you can please help it will be nice. I can transpose the columns but I trying to transpose with a calculation (measure). Thanks.

Re: Table Transformation with DAX

Whitewater100 — Sat, 26 Mar 2022 21:38:56 GMT

Hi Vikrant:

Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

Sorry if this doesn't do the table answer you'd like.

Here is one measure that produces the same results:(with the Date Table in Model)

MOM % Avg Price 2 =

var monthavg = AVERAGEX(VALUES(Dates[Month]),

average(TableA[Price])

)

var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))

var currprice = SELECTEDVALUE(TableA[Price])

var pricediff = monthavg - PrevMontPrice

var monumber = SELECTEDVALUE(Dates[Month No.])

return

IF( NOT(ISBLANK(currprice) && monumber >=2),

DIVIDE(pricediff, [Prev Mont Price]))

The Generate Table function might help here, not entirely sure...

Re: Table Transformation with DAX

VikrantC — Sat, 26 Mar 2022 22:15:32 GMT

Hi Whitewhater100,

Thank you very much. Unfortunately, it did not work.

Regards,

Re: Table Transformation with DAX

Whitewater100 — Sun, 27 Mar 2022 13:19:06 GMT

Hi:

I checked the measure and it is working. I have added a table with the measure. You can fliter out months as needed or maybe you want to see the months for future calculations.https://drive.google.com/file/d/1POgyUikMWl4Oywy75Z1v9QCs8lbUC-DY/view?usp=sharing

I hope this is closer.

Re: Table Transformation with DAX

VikrantC — Sun, 27 Mar 2022 15:35:34 GMT

Hi Whitewater100,

Thank you fro trying. Basically, your results are same as my Table2. What I am trying to do is not create the table2 but directly create Table3. In my original post the Table3 is basically there are three columns: Date, productA and ProductB. The rows are MOM%. I can do the price but NOT the MOM%.

Again, thanks for you trying and helping.

Regards,

Re: Table Transformation with DAX

tamerj1 — Sun, 27 Mar 2022 17:21:57 GMT

Hi VikrantC

You may try

Table3 = ADDCOLUMNS ( SUMMARIZE ( Table1, Calender[End Of Month] ), "Product A%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product A" ), "Product B%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product B" ) )

Re: Table Transformation with DAX

VikrantC — Sun, 27 Mar 2022 18:36:51 GMT

Awesome, This worked. Thank you very much.

Re: Table Transformation with DAX

tamerj1 — Sun, 27 Mar 2022 18:55:15 GMT

Great VikrantC

If it solved your problem, would you please consider marking my reply as accepted solution?

Re: Table Transformation with DAX

Whitewater100 — Mon, 28 Mar 2022 13:07:18 GMT

Hi VC & TJ:

Tamerj answered correctly and I beleive I mistakenly received credit. Not sure how to ammend that. Thank you. FYI.