Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a problem with a data model table connected through Direct Query. The table has customer information, and there I have columns that should be grouped into one, such as the survey date (date when different surveys were done), it looks like this:
The "Recent Date" column indicates the last survey answered. My question is how to unmout the pivot of columns without Power Query, because the Direct Query connection does not allow me to.
My goal is to record all completed surveys in a single date column.
I hope I've explained myself correctly,
I appreciate it!
Solved! Go to Solution.
Lett's assume the 'Original' table is:
You can create a new table using DAX with the following:
Unpivoted Table =
VAR F0 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente]
),
"Fase", "Fecha Reciente"
)
VAR F1 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -1 ]
),
"Fase", "Fecha Reciente -1"
)
VAR F2 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -2]
),
"Fase", "Fecha Reciente -2"
)
VAR F3 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -3 ]
),
"Fase", "Fecha Reciente -3"
)
VAR F4 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha reciente -4]
),
"Fase", "Fecha Reciente -4"
)
RETURN
UNION ( F0, F1, F2, F3, F4 )
and you get this:
Proud to be a Super User!
Paul on Linkedin.
Lett's assume the 'Original' table is:
You can create a new table using DAX with the following:
Unpivoted Table =
VAR F0 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente]
),
"Fase", "Fecha Reciente"
)
VAR F1 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -1 ]
),
"Fase", "Fecha Reciente -1"
)
VAR F2 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -2]
),
"Fase", "Fecha Reciente -2"
)
VAR F3 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha Reciente -3 ]
),
"Fase", "Fecha Reciente -3"
)
VAR F4 =
ADDCOLUMNS (
SELECTCOLUMNS (
'Original Table',
"ID Cliente", 'Original Table'[ID Cliente],
"Fecha", 'Original Table'[Fecha reciente -4]
),
"Fase", "Fecha Reciente -4"
)
RETURN
UNION ( F0, F1, F2, F3, F4 )
and you get this:
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |