transpose some measure/ merge

[puserp]

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 ?

[Anonymous · Accepted solution]

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.

[bhanu_gautam]

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

[puserp]

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