Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |