Forum Discussion

OskariNi's avatar
OskariNi
Icon for Helper I rankHelper 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]...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Helper I rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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 )  
        )
      • OskariNi's avatar
        OskariNi
        Icon for Helper I rankHelper I
        8 years ago

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