Forum Discussion
Post PatronCreate new Table using Measures and selected Columns from Another Table
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...
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.⚡
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.⚡
5 Replies
- parry2k
Super User
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
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.⚡
roncruiserparry2k
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.
- mahoneypat
Microsoft Employee
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
- 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.
