Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chefe
Helper II
Helper II

how to pivot a table in-memory? using summarize+addcolumns?

Hello

 

A certain record (id = "#1") ahs to numeric values stored in separates lines, c.f. screenshot. How can pivot/merge/join these two lines to so that the result is a 1-line-table stored in-memory?

 

I think SUMMARIZE in connection with ADDCOLUMNS seems to be possible, way, but I could not yet really succeed.

 

Maybe this is quite simple and somebody knows the DAX formula to get to the result in the screenshot?

 

Cheers

 

2016-05-10 17_26_35-del.xlsx - Excel.png

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

Hi Chefe,

 

According to your description, you need to create a new table based on the exist table, right?

 

I have tested it on my local environment, here is the sample DAX for you reference.

Create a table
NewTable = SUMMARIZE('Summary',Summary[ID])
Create columns
Amount1 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
Unit1 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
Amount2 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
Unit2 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
Amount3 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")
Unit3 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")

 

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

View solution in original post

6 REPLIES 6
v-caliao-msft
Employee
Employee

Hi Chefe,

 

According to your description, you need to create a new table based on the exist table, right?

 

I have tested it on my local environment, here is the sample DAX for you reference.

Create a table
NewTable = SUMMARIZE('Summary',Summary[ID])
Create columns
Amount1 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
Unit1 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg1")
Amount2 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
Unit2 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg2")
Amount3 = LOOKUPVALUE('Summary'[Amount],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")
Unit3 = LOOKUPVALUE('Summary'[Unit],'Summary'[ID],'NewTable'[ID],'Summary'[Leg],"Leg3")

 

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

Hi Charlie

 

Thanks. Correct, but in-memory, since I do not want to add any more phyisical table objects to my data model.

Do you mind sharing that specific pbix file of yours? 

 

Cheers, chefe

 

 

++++++++++++ edit ++++++++++++

 

Now I got it, thank you. Don't need to share the file now 🙂

 

I was totally unaware of the "calculate table" functionality 😮 making tables using DAX syntax that otherwise could only be seen using something like DAX Studio (which I cannot install on my machine unfortunately...), so it is the same coding to use in a measure... brilliant. Thanks again 😉

 

Though I did have to alter your code a bit:

* using =addcolumns() in the formula instead of - I guess - adding calculated columns using the UI

 

The syntax - which now should be usable as an interim step in any measure (?) - is now as follows:

 

MyNewTable = 
ADDCOLUMNS(SUMMARIZE(Table2;Table2[id])
	;"fx leg amount"
	;LOOKUPVALUE(Table2[amount]
		;Table2[leg #]
		;"leg1"
		;Table2[id]
		;[id]
	)
	;"fx leg ccy"
	;LOOKUPVALUE(Table2[unit]
		;Table2[leg #]
		;"leg1"
		;Table2[id]
		;[id]
	)
	;"base leg amount"
	;LOOKUPVALUE(Table2[amount]
		;Table2[leg #]
		;"leg2"
		;Table2[id]
		;[id]
	)
	;"base leg ccy"
	;LOOKUPVALUE(Table2[unit]
		;Table2[leg #]
		;"leg2"
		;Table2[id]
		;[id]
	)
)

Problem solved. Thanks again.

kcantor
Community Champion
Community Champion

@chefe

Have you tried "Group by" in the Query Editor? It is on the Transform Tab and should provide the results you are seeking.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for the quick reply! I forgot to mention, that I do not wish to physically create a new table. I wish to use this in-memory table as an interim step in a measure calculation.

kcantor
Community Champion
Community Champion

Then why not just write up the SUM measure/calculation. Use the identifying code on the rows and it should create the totals you need by code. Then use that calculation within the next step.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Tried the same scenario with a number of DAX functions but didn't succeed either 😞 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.