Create new Table using Measures and selected Columns from Another Table

[roncruiser]

Hello,

I have a table in which I've created quite a few measures.

I'd like to create a new table using the current table columns highlighted in green, and include the measures highlighted in yellow.
Something like the pivot function does in Power Query but done with DAX.

Basically pivoting columns Meas0, Meas1, Meas2, Meas3 in yellow.

Ending up with 2 new columns "Meas" and "Value".

New Columns...:

"Meas: column will contains the  different measures.

"Value" columns will contains the numerical values from the pivot columns.

New Table Columns

Set1,ID, Set2,Set3,Set4,Set5, Meas, Value

Current Table:

Need help with this one...

[parry2k · Accepted solution]

@roncruiser try something like this:

New Table =
UNION (
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas0", "value", [Meas0 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas1", "value", [Meas1 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas2", "value", [Meas2 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas3", "value", [Meas3 Measure] )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@mahoneypat great idea, but the last reply from @roncruiser  will not be solved by any of those two options but I agree with you.

[parry2k · Accepted solution]

@roncruiser something like this then:

New Table =
UNION (
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas0", "Meas1", [Meas1 Measure], "value", [Meas0 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas1", "Meas1", [Meas1 Measure], "value", [Meas1 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas2", "Meas1", [Meas1 Measure], "value", [Meas2 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas3", "Meas1", [Meas1 Measure], "value", [Meas3 Measure] )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mahoneypat]

Have you considered a matrix visual and using the Show On Rows feature for your measures?  If that doesn't work, this may be easier with a Calculation Group (using the Calculation Items pointing to your measures and used on the Rows of the matrix).

Pat

[roncruiser]

@parry2k 

I'll give a try.  AFK at the moment.
What if I want to keep one of the measures in it's own column in the newly created table?  Not pivot with the other measures.  For example "Meas1" would now be green.

Thank you for the timely response.  It's going to be a long night.

[parry2k · Accepted solution]

@roncruiser try something like this:

New Table =
UNION (
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas0", "value", [Meas0 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas1", "value", [Meas1 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas2", "value", [Meas2 Measure] ),
SUMMARIZE ( YourTable, YourTable[Column1], YourTable[Column2],YourTable[Column3], "Meas", "Meas3", "value", [Meas3 Measure] )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡