Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 🙂
Solved! Go to 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
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
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
Hi @Pragati11
I posted a detailed reply, but somehow it was marked as spam by the website.
Is it possible to "unspam" my reply?
Hi @Anonymous ,
I can't see your response.
May be try posting it again or message admin.
Thanks,
Pragati
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |