Override column dynamization with DAX

[Syndicate_Admin]

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!

[PaulDBrown · Accepted 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: