Forum Discussion
transpose some measure/ merge
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 ?
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- bhanu_gautam
Super User
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" )
)Thanks, I just can't get the syntax to work, it gives meThe column "Measure1" in the table "Table 1" is not found and is not used in this expressionThat last one was on me for wrong source of t1 and t2
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
