DAX or Custom table creation by joining columns from multiple tables

[Anonymous]

Hi Team,

Hope everyone is doing good!

Need help in achieving the below scenerio. we are using Sharepoint online list as datasource to fetch the data. But the real problems comes with data refresh , as because lot of steps are added on the table to get the clean data. What i did notice was things like Merge Queries (used Inner join) , expand columns (post merge to get the loook up table related columns) etc. are taking very long time to refersh So i tested  the scenerio by removing all these extractions and just refreshing the raw data as it comes from sharepoint online, then the refresh completed in minutes.

But, that is not suffice. We need to go for merge queries option as the custom reports can't be made just with the raw data as is . when discussed with Microsft team, they said there are issues with SharePoint online connector and trying to fix them. Eventhough the fix is released, the mid air token validity will be 2 hrs max. if this is the case, then all the reports will definetly fail if the data grew. 

So i am testing in multiple ways to solve the issue. So i am thinking of getting raw data with limited transformation like removing metadata related columns, expanding person or group column and then apply dax expressions to establish relations between the data and create reports. As part of this exercie, i wanted know how to establish a report that involves columns from multiple tables in a single table visual with one-to-many relationship established. Below image helps you to visualize the scenerio.

 

Lets assume below are the data tables

Product Description:

Product ID	Product Name
1	SUV
2	Muti Axle Bus
3	Hatch Back

Vendor:

Product ID	Vendor Name
1	Nissan
1	Jaguar
2	Volvo
2	Scania
3	Volkswagen
3	Hyundai

item cost:

Product ID	Item Description	Cost(in USD)
1	Interior Design	3000
1	Body Design	4000
2	Interior Design	100000
3	Interior Design	9000
3	Body Design	11000

Total Cost:

1	7000
2	100000
3	20000

The consolidated report should like below:

Product ID	Product Name	Vendor	Item Description	Cost(in USD)	Total Cost
1	SUV	Jaguar	Interior Design	3000	7000
1	SUV	Jaguar	Body Design	4000	7000
2	Multi Axle bus	Volvo	Interior Design	100000	100000
3	Hatch Back	Hyundai	Interior Design	9000	20000
3	Hatch Back	Hyundai	Body Design	11000	20000

Please help in arriving at the above scenerio. I know creating custom table using merge queries with inner join is an option. but as sharepoint list as a connector, its not working great and it is taking ages to complete the refresh. 

Thanks for being patience in reading the entire case scenerio.

[Anonymous]

So... If I simplify the question... are you saying "given these 4 tables of data that came from Sharepoint... how can I use DAX to create the consolitated report" ?

[Anonymous]

Yes. Instead of clixking edit queries and use merge queries. I am looking for a possibility to create a consolidate report using DAX. Any help

[Anonymous]

Hi Team,

Can someone help me with this requirement ?

Regards,

Sivapratap