Forum Discussion

OskariNi's avatar
OskariNi
Helper I
8 years ago

Transpose with DAX

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

 

Need Help

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      OskariNi
      Helper I
      8 years ago

      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's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        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 )  
        )