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

v-lili6-msft's avatar
v-lili6-msft
Community Support
5 years ago

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