DAX unpivot

[Anonymous]

Hi 

In the DAX formula I would like to have the effect of a column that identify the "transaction"/accounting type.

the column would contain eg Gross UPR movement, GrossPremium and RIIBNRMove next to the relevant rows.

please help! 🙂

[v-lili6-msft]

hi  @Anonymous 

If you want to use dax to unpivot the table, you could refer to this blog:

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832

And if you want to get further help, please share your sample data and your expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Pragati11]

Hi @Anonymous ,

I don't understand what you are trying to ask here. Please refer following link on how to post your query:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

I posted a detailed reply, but somehow it was marked as spam by the website.

Is it possible to "unspam" my reply?

[Pragati11]

Hi @Anonymous ,

I can't see your response.

May be try posting it again or message admin.

Thanks,

Pragati

[Anonymous]

this response keeps getting marked as spam
@Pragati11  @v-lili6-msft 

how do I contact administrator?

[Anonymous]

hi @Pragati11 , @v-lili6-msft 

- I need to group by multiple DIM and FACT tables and then unpivot it using DAX

- Unfortunately I cant use the GROUP BY and UNPIVOT functions in PowerQuery due my work being done in Visual Studio/Analysis Services as a Tabular model (compatibility limited to 2016 for our clients, otherwise I could use PowerQuery)

- DIM_Tables:  DIM_Product: ID, ProductName ;

                        DIM_Company: ID, CompanyName ;

                        DIM_AccountingPeriod: ID, AccPeriodMonth

- FACT Tables: FACT_Sales:            ProductID, CompanyID, AccPeriodMonthID, SalesAmount ; 

                        FACT_CostOfSales: ProductID, CompanyID, AccPeriodMonthID, CostOfSalesAmount ;

                        FACT_AdminCosts: ProductID, CompanyID, AccPeriodMonthID, AdminCosts

- I need the following output:

ProductName  |   AccPeriodMonth   |    Companyname   |    TransactionType               |   Amount

1                      |     Jan20                   |        A                     |       Sales                              |     100
2                      |     Jan20                   |        A                     |       CostOfSales                   |       50
3                      |     Jan20                   |        A                     |       AdminCosts                   |       20
1                      |     Feb20                  |        A                      |       Sales                              |     200
2                      |     Feb20                  |        A                      |       CostOfSales                   |       70
3                      |     Feb20                  |        A                      |      AdminCosts                    |       30
1                      |     Jan20                   |        B                     |       Sales                              |     100
2                      |     Jan20                   |        B                     |       CostOfSales                   |       50
3                      |     Jan20                   |        B                     |       AdminCosts                   |       20
1                      |     Feb20                  |        B                      |       Sales                              |     200
2                      |     Feb20                  |        B                      |       CostOfSales                   |       70
3                      |     Feb20                  |        B                      |      AdminCosts                    |       30

I appreciate the help.

Thank you in advance

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

You could try this formula:

Table = 
UNION( 
SELECTCOLUMNS(FACT_Sales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","Sales","Amount",[SalesAmount]),

SELECTCOLUMNS(FACT_CostOfSales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","CostOfSales","Amount",[CostOfSalesAmount]),

SELECTCOLUMNS(FACT_AdminCosts,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","AdminCosts","Amount",[AdminCosts]

))

result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Thank you very much @v-lili6-msft 
Much appreciated.
I can see it works, but perhaps my data set is too large or complex, because it won't execute the DAX. It hangs and then crashes.