Forum Discussion

jaipalambati's avatar
jaipalambati
Frequent Visitor
3 years ago

pivot/unpivot table using DAX

Hello, can somebody help me to create new table using DAX with filters and calculations please.

 

I have a table as follows:

DateClientTypeItemValue
1/12/2022clienttype 1item 15
2/12/2022clienttype 1item 15
3/12/2022clienttype 1item 15
1/12/2022clienttype 2item 25
2/12/2022clienttype 2item 25
3/12/2022clienttype 2item 25
1/12/2022clienttype 3item 15
2/12/2022clienttype 3item 15
3/12/2022clienttype 3item 15
1/12/2022clienttype 4item 25
2/12/2022clienttype 4item 25
3/12/2022clienttype 4item 25
1/12/2022clienttype 5item 30
2/12/2022clienttype 5item 30
3/12/2022clienttype 5item 30
1/12/2022clienttype 5item 40
2/12/2022clienttype 5item 40
3/12/2022clienttype 5item 40
1/12/2022clienttype 5item 50
2/12/2022clienttype 5item 50
3/12/2022clienttype 5item 50

and I need to achieve data in a new table as below using DAX only:

Date  Client  Type  item 1  item 2  item 3  item 4  item 5Totals
1/12/2022  clienttype 5  0  0  0  0  0  0
1/12/2022  clienttype 4  0  5  0  0  0  5
1/12/2022  clienttype 3  5  0  0  0  0  5
2/12/2022  clienttype 5  0  0  0  0  0  0
2/12/2022  clienttype 4  0  5  0  0  0  5
2/12/2022  clienttype 3  5  0  0  0  0  5
3/12/2022  clienttype 5  0  0  0  0  0  0
3/12/2022  clienttype 4  0  5  0  0  0  5
3/12/2022clienttype 3  5  0  0  0  0  5
         

So basically, I need to filter "Type" column by type3, type 4, type 5 and Pivot "Item" & "value" columns by "Value" and create a new column called "Totals". So the Total column will be Sum(Item)

 

I know this will be easy doing in Power Query, by I want to achieve this with DAX.

 

TIA

 

 

 

 

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    jaipalambati , You can display that in Matrix like that. That should be best

     

    If you still need pivot.

    In DAX we do not have a pivot. You have to create measures for each one of those

    Item 1= calculate(Sum(Table[Value]), filter(Table, Table[Item] = "Item 1") )

     

    You can create other measures