DAX expression: create a new table from table reshaping

Question

I am new to Power BI and perhaps this is a very stupid question.

What I would like to achieve is to create a new table based on the original table by reshaping it, using DAX.

The original table looks like:

ID  Start   End
1   Sep 2013    Oct 2013
2   Oct 2013    Jan 2014
3   Oct 2013    Dec 2013
4   Oct 2013    Nov 2013
5   Nov 2013    Dec 2013
6   Nov 2013    Jan 2014

The new table will use the values in "Start" and "End" as the row names, and the count of "Start" and "End" in each month-year as the values. This means the expected, reshaped new table looks like:

MonthYear   StartCount  EndCount
Sep 2013    1   0
Oct 2013    3   1
Nov 2013    2   1
Dec 2013    0   2
Jan 2014    0   2

I would like to learn if you have any idea how to reshape the table using DAX.

mxix mxix · Accepted Answer · 2019-03-20T18:12:50

If you are trying to create a calculated table, this should return your expected output:

New Table =
ADDCOLUMNS (
    SELECTCOLUMNS (
        SUMMARIZE (
            UNION ( VALUES ( Data[Start] ); VALUES ( Data[End] ) );
            Data[Start]
        );
        "Date"; [Start]
    );
    "StartCount"; CALCULATE ( COUNTROWS ( Data ); Data[Start] = EARLIER ( [Date] ) );
    "EndCount"; CALCULATE ( COUNTROWS ( Data ); Data[End] = EARLIER ( [Date] ) )
)

With UNION and SUMMARIZE I am getting the distinct values you have for the dates, using SELECTCOLUMNS just for creating the Alias. With ADDCOLUMNS using it to create the scope for the CALCULATEs to work on with the use of EARLIER for filtering the data.

DAX expression: create a new table from table reshaping

1 Answers