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

[chefe]

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

[v-caliao-msft · Accepted solution]

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")

Regards,

Charlie Liao

[chefe]

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]

@chefe

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

[chefe]

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]

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.

[alexpancakes]

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