Better performance on JOINT DAX

[NickProp28]

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

[v-cazheng-msft · Accepted solution]

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.

[amitchandak]

@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/

[NickProp28]

Dear @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.