Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
OskariNi
Helper I
Helper I

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

 

 Capture2.PNG

 

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

 

6 REPLIES 6
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you

Phil_Seamark
Microsoft Employee
Microsoft Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.