Reshape data to simplify visual plot

[redzum]

Hi All,

I'm new here and beginner in PBI. I have data originally with 5 columns and to be manipulated for visuals (line & bar chart). I have to introduce new column to re-shape & calculate the different between subsequent event of data as compared to 1st data available for every unit ID. The new column is to be introduced for visual plot (column F untill K). I attached here the perfect data arrangement (in excel format) that i need  to do in PBI. 

Question here what is DAX formula that i can i apply to have value at firstdate for "PCP subsidence", "SCP Subsidence" and "CCP Subsidence" to be in new column of "PCP_1", "SCP_1" and "CCP_1" for every unique ID.  The reason of sorting like this is to simplify the calculation of subseqeunt event for unique ID with first value on the firstdate. 

Thank you in advance for any feedback & help.

[MattAllington]

I have produced a small sample workbook for you.

https://www.dropbox.com/s/cwcgduqi7efdath/reshape.pbix?dl=1

I unpivoted the data into a different shape that makes the code more efficient.  Take a look and see if you can work out how it works. Let me know if you have questions.

Matt

[redzum]

Hi Matt,

Thanks for help, really appreciate it. Actually original file are with many columns with other parameters more than what i provided before, see as attached. That is the reason why i need to find a way to re-shape the data at the 1st place. Perhaps i need to reshape the data using VBA in excel prior import to PBI if DAX is not possible to do it?

  

Redzuan

[MattAllington · Accepted solution]

Unpivoting as a principle should not be impacted by how many columns you have. I don't think you need to use VBA in preference to Power Querr

[redzum]

Hi Matt,

I managed to automate by using unpivot in data query. This is what i need. Thanks for the solution. Awesome!

Thanks

Redzuan