Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX unpivot

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    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

Pragati11's avatar
Pragati11
Super User
5 years ago

Hi Anonymous ,

 

I can't see your response.

May be try posting it again or message admin.

 

Thanks,

Pragati

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    v-lili6-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.