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

 

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