topic Re: Need Help with Creating Table from 4 Different Datasets in DAX Commands and Tips

Need Help with Creating Table from 4 Different Datasets

cyborgandy — Wed, 23 Nov 2022 15:09:36 GMT

I have 4 different excel dataset connected to power bi ,below are the example of different dataset.

Invoice Date	Paid Fare Inc Tax	Cost Centre1	Product Area
Monday, January 31, 2022	11.7	ABCD	Rail
Monday, January 31, 2022	11.7	ABCD	Rail
Monday,July 31, 2022	11.7	ABCD	Rail
Monday,August 31, 2022	11.7	ABCD	Rail



Actual Cost	Cost Centre	Product Area
135	ABAD	Hotel
135	ABAD	Hotel
85.5	ABAD	Hotel
97	ABAD	Hotel
97	ABAD	Hotel


Month	GBPGross Cost	Cost Centre 1	Product Area
February	150	EEEE	Conference
January	1000	EEEE	Conference
February	250E	EEEE	Conference
February	250	EEEE	Conference
January	720	EEEE	Conference

Travel Month	Ticket Number	Paid Fare inc Air Tax	Cost Centre1	Product Area
May	12345	119.96	YYYY	Air
May	56789	151.96	YYTY	Air
May	22345	279.96	YYAB	Air
March	34556	70.48	YYYY	Air
May	22334	161.98	YYCA	Air

I need to build below table in power bi,could someone help if it is possible as the value is in different datasets.

Product Area	Previous Month Spend	Current Month Spend
Air	XXXX	XXXX
Confrence	XXXX	XXXX
Rail	XXXX	XXXX
Hotel	XXXX	XXXX

Re: Need Help with Creating Table from 4 Different Datasets

ERD — Wed, 23 Nov 2022 16:54:29 GMT

cyborgandy ,

1. Your second table doesn't have dates. Does the column name 'Actual Cost' mean that you always have values for CURRENT month only in this table? If so, in your resulting table you will only have 'Current Month Spend' for the 'Hotel' Product Area. If not, it will be impossible to determine values for Previous/Current Month Spend.

2. I would append processed tables in Power Query first and then you might create some measures as an option...

Re: Need Help with Creating Table from 4 Different Datasets

FreemanZ — Thu, 24 Nov 2022 02:51:47 GMT

could you also explain the relationships between the four tables?

Re: Need Help with Creating Table from 4 Different Datasets

v-xiaosun-msft — Thu, 24 Nov 2022 09:46:33 GMT

Hi cyborgandy ,

I wonder whether you can describe more clearly about the calculation logic of "Previous Month Spend" and "Current Month Spend" which you need in your output and the relationships between four tables. Provide clear description in order that we can help you further more.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need Help with Creating Table from 4 Different Datasets

NikhilChenna — Thu, 24 Nov 2022 09:57:09 GMT

Hi cyborgandy , i think its simple.
1. Create a new table with one column having all unique values of "Product Area".
2. Create one to many relationship from you new table to all the 4 tables.
3. Come to your report page and drag, product area column from your new table and drag all reqiured columns from the other four tables.

Regards,
Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!