Transpose with DAX

[OskariNi]

Hi all,

I have calculated new columns (week17-week20) with DAX formulas from another table ('Excel').

( week20 = Table1[week19]+CALCULATE(COUNTA('Excel'[Week]);'Excel'[Week]=20;'Excel'[State]="Open")-CALCULATE(COUNTA('Excel'[Week]);'Excel'[Week]=20;'Excel'[State]="Closed")

 

These numbers represent the amount of Open workorders at the end of the week. I would like to draw curve with these values (axis=weeks, values=open workorders), but to do that I have to transpose the table into two columns. Is it possible to do that with DAX? I can't do it with power query because the DAX-calculated valued doesn't show there. 

Thank's, Oskari

[v-yulgu-msft]

Hi @OskariNi,

These numbers represent the amount of Open workorders at the end of the week. I would like to draw curve with these values (axis=weeks, values=open workorders), but to do that I have to transpose the table into two columns. Is it possible to do that with DAX?

You can new an other calculated table using UNION() function.

NewTable =
UNION (
    SELECTCOLUMNS ( Table1, "Week", "Before", "Value", Table1[Before] ),
    SELECTCOLUMNS ( Table1, "Week", "week17", "Value", Table1[week17] ),
    SELECTCOLUMNS ( Table1, "Week", "week18", "Value", Table1[week18] ),
    SELECTCOLUMNS ( Table1, "Week", "week19", "Value", Table1[week19] ),
    SELECTCOLUMNS ( Table1, "Week", "week20", "Value", Table1[week20] )
)

Best regards,

Yuliana Gu

[makoknam]

Thank you

[Phil_Seamark]

Hi @OskariNi

This calculated table might get close

Table = 
    SUMMARIZECOLUMNS(
        'Excel'[Week],
        "Value",
        CALCULATE(COUNTA('Excel'[Week]),'Excel'[State]="Open")
        -
        CALCULATE(COUNTA('Excel'[Week]),'Excel'[State]="Closed")
        )

[OskariNi]

Hi @Phil_Seamark,

It's close but the problem is that I need to use the situation of the previous week as a starting point for the next week. This solution gives me the difference between closed and opened per week not the amount of open workorders. 

Oskari

[Phil_Seamark]

Hi @OskariNi

Is this better?

Table = 
    SUMMARIZECOLUMNS(
        'Excel'[Week],
        "Value",
        VAR x = MIN('Excel'[Week])
        RETURN 
         CALCULATE(COUNTA('Excel'[Week]),'Excel'[State]="Open",'Excel'[Week]=x )
         -
         CALCULATE(COUNTA('Excel'[Week]),'Excel'[State]="Closed",'Excel'[Week]=x-1 )  
        )

[OskariNi]

Hi @Phil_Seamark

This way below it works for the 2 first weeks (17 and 18) but then 19 is wrong because it doesn't take into account week 17 (it requires a cumulative sum). It would be really easy if I just could flip the row to form a column but I guess that is not possible?

Table2 = SUMMARIZECOLUMNS(

        ‘Excel’[Week];

        "Value";        VAR x = MIN(‘Excel’[Week])

        RETURN

        CALCULATE(COUNTA(‘Excel’[Week]);’Excel’[State]="Open";’Excel’[Week]=x)

        -

        CALCULATE(COUNTA(‘Excel’[Week]);’Excel’[State]="Closed";’Excel’[Week]=x)

        +

        CALCULATE(COUNTA(‘Excel’[Week]);’Excel’[State]="Open";’Excel’[Week]=x-1)

         -

        CALCULATE(COUNTA(‘Excel’[Week]);’Excel’[State]="Closed";’Excel’[Week]=x-1))