Search code examples
powerbidaxpowerquerymbridge

Bridge tables - DAX or M?

Should we construct bridge tables with DAX or M?

enter image description here

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?

Solution

  • 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...