topic Re: transpose some measure/ merge in DAX Commands and Tips

transpose some measure/ merge

puserp — Tue, 30 Apr 2024 14:46:28 GMT

I have a query resulting in :

[TABLE 1]

Dim1	Dim2	Dim3	Date	Measure1	Measure 2
a	xyz	abc	t1	m1t1a	m2t1a
a	xyz	abc	t2	m1t2a	m2t2a
b	bxyz	abcz	t1	m1t1b	m2t1b
b	bxyz	abcz	t2	m1t2b	m2t2b

I want to get the results as

[TABLE 2]

Dim1	Dim2	Dim3	Measure1 t1	Measure 2 t1	Measure1 t2	Measure 2 t2
a	xyz	abc	m1t1a	m2t1a	m1t2a	m2t2a
b	bxyz	abcz	m1t1b	m2t1b	m1t2b	m2t2b

In python that can be done easily with two queries for date 1 and date 2 and then a merge on Dim1 Dim2 DIm 3 with suffixes t1 and t2.
Can I achieve directly my [Table 2] in DAX ?

Re: transpose some measure/ merge

bhanu_gautam — Tue, 30 Apr 2024 15:42:09 GMT

puserp , Try using below method

Table2 =
SUMMARIZECOLUMNS (
'TABLE 1'[Dim1],
'TABLE 1'[Dim2],
'TABLE 1'[Dim3],
"Measure1 t1", CALCULATE ( SUM ( 'TABLE 1'[Measure1] ), 'TABLE 1'[Date] = "t1" ),
"Measure 2 t1", CALCULATE ( SUM ( 'TABLE 1'[Measure2] ), 'TABLE 1'[Date] = "t1" ),
"Measure1 t2", CALCULATE ( SUM ( 'TABLE 1'[Measure1] ), 'TABLE 1'[Date] = "t2" ),
"Measure 2 t2", CALCULATE ( SUM ( 'TABLE 1'[Measure2] ), 'TABLE 1'[Date] = "t2" )
)

Re: transpose some measure/ merge

puserp — Thu, 02 May 2024 07:30:52 GMT

~~Thanks, I just can't get the syntax to work, it gives me~~

~~The column "Measure1" in the table "Table 1" is not found and is not used in this expression~~

That last one was on me for wrong source of t1 and t2

Re: transpose some measure/ merge

Anonymous — Wed, 01 May 2024 05:50:31 GMT

Thanks for the reply from bhanu_gautam , please allow me to provide another insight:

Hi puserp ,

Whether the formula works or not depends on whether the measure1, measure2 in the information you provide is a created measure or a specific field in the table. Refer to the following formula:

if measure, try below formula:

Table2 = SUMMARIZECOLUMNS ( 'Table'[Dim1], 'Table'[Dim2], 'Table'[Dim3], "Measure1 t1", CALCULATE ( [Measure1], 'Table'[Date ] = "t1" ), "Measure 2 t1", CALCULATE ( [Measure2], 'Table'[Date ] = "t1" ), "Measure1 t2", CALCULATE ( [Measure1], 'Table'[Date ] = "t2" ), "Measure 2 t2", CALCULATE ( [Measure2], 'Table'[Date ] = "t2" ) )

if field, try below formula:

Table3 = SUMMARIZECOLUMNS ( 'Table'[Dim1], 'Table'[Dim2], 'Table'[Dim3], "Measure1 t1", CALCULATE ( MAX ( 'Table'[M1] ), 'Table'[Date ] = "t1" ), "Measure 2 t1", CALCULATE ( MAX ( 'Table'[M2] ), 'Table'[Date ] = "t1" ), "Measure1 t2", CALCULATE ( MAX ( 'Table'[M1] ), 'Table'[Date ] = "t2" ), "Measure 2 t2", CALCULATE ( MAX ( 'Table'[M2] ), 'Table'[Date ] = "t2" ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.