Forum Discussion
Post PartisanBetter performance on JOINT DAX
Dear Community,
Needs some helps on improve crossjoin performance.
Here's the code in SQL form,
SELECT a.ConsolID, a.ContainerMode, a.ConsolTransport ,a.ConsolType, a.FirstLoad, a.LastDispatch, b.UniqueMatchColumn,b. NEE/Con, a.ChargeableKG, a.ChargebleM3,b.DelimitedBranch, a.ETD
FROM MergeShipment a INNER JOIN Client b on
a.ConsolID = b.ConsolNumber
And here's the Joint code I have in BI. But this code took me around 9-10 hours whenever I weekly refresh the huge raw data. Hopefully someone can give some advice on this code revise for better performance.
FinalizeWeight = Summarize(FILTER(CROSSJOIN(MergeShipment,Summarize(Clients,Clients[Consol Number],Clients[Unique Match Columnsss],Clients[DelimitedBranch],Clients[NEE/CON])),MergeShipment[Consol ID]=Clients[Consol Number]),
[Consol ID],[ContainerMode],[ConsolTransport],[ConsolType],[FirstLoad],[Last Dispatch],[Unique Match Column],[NEE/CON],[ChargeableKG],[ChargeableM3],[Delimited_Branch],[ETD])
There have other option rather than using DAX code, but
the first reason I cannot use append queries option in query editor due to the column UniqueMatchColum is a measure column, Hence, I cant use this option.
Secondly, i cant physical join relationships in the data model due to there have many-many relationship, and this is why I incldude Summarize in DAX code.
Any help provided will appreciated! Thanks
Hi NickProp28
If the values of the Measures are relative stable and they don't need to change with other context. You can use Custom column in Power Query to replace it and then merge queries. Or you can consider filter rows with same ‘Consol ID’ and ‘Consol Number’ firstly, then join these tables.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
NickProp28 , first of all, avoid cross join.
Do you need to merge the table?
You can join two tables and put columns
You merge in power query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
or use natural inner join
refer https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
NickProp28Dear amitchandak ,
Thanks for the response. Yes, I need to merge the column from these tables and form a new table
As mentioned, I cant use the power query because one of the column named 'UniqueMatchColum' is a new measure column and it doesnt show in power query.
Next, because this two table have duplicate issue and result that having many-many relationship. Which is why i cant apply natural inner join.
v-cazheng-msftCommunity Support
Hi NickProp28
If the values of the Measures are relative stable and they don't need to change with other context. You can use Custom column in Power Query to replace it and then merge queries. Or you can consider filter rows with same ‘Consol ID’ and ‘Consol Number’ firstly, then join these tables.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
