Should we construct bridge tables with DAX or M?
Picture stolen from here
It seems very tempting to use DAX. With DAX the code is short and clear:
IDList = DISTINCT(
UNION(
DISTINCT(Table1[ID])
,DISTINCT(Table2[ID])
))
Moreover, DAX tables do not need to be loaded as M tables. However I wonder if advantage of DAX over M is not illusory? M seems to load once and DAX seems to be calculated on the fly, maybe anytime, over and over?
DAX calculated tables are re-calculated if any of the tables it pulls data from are refreshed or updated in any way. (from https://learn.microsoft.com/en-us/power-bi/desktop-calculated-tables )
They're not re-calculated "on the fly", nor "over and over". There's no difference to the refresh cycle of your Power BI data model, between using a DAX calculated table or an M query table. You may however find that DAX calculated tables refresh faster than M, depending on the complexity of the table...
Considering M Tables, M Conditional Columns, M Custom Columns, DAX Tables, DAX Calculated Columns and DAX Measures. It is only the DAX Measures that gets created on the fly and is not a part of the data model.
So for a Simple Bridge Table, DAX Table and M Table have no real advantage over each other.
Both Tables allows one to create relationships. Now, When I say simple Bridge Table, it is something that is created from 2 or 3 tables and uses the same column to establish relationships with two or more tables.
But when the requirements become complex and agile (growing over time), the maintenance and the developments efforts also increases, if it is created by DAX. (my personal opinion and I think most people's personal opinion as well.)
If it is created by M, then it is more easy to add a new column or filter based on a logic or to replace an existing value.
Taking back to the Thumb Rule :- If it is created by DAX, then M Cannot be used on top of it to make changes. So, if the Bridge Table is created by DAX, then it won't appear in the query editor and limits the advantages of GUI to make any required simple transformations in the data.
For a Simple Bridge Table :- DAX.
But For a complex and changing requirement :- M.
