Forum Discussion

tomhawley's avatar
tomhawley
Icon for Advocate I rankAdvocate I
10 years ago
Solved

DAX Functions - Level of Support

Can anybody explain the level of DAX support in Power BI? Anything I read seems to suggest it is *the* supported language, but I find I keep running into unsupported functions that often seem to have equivalents in Power BI with different names.

 

For example, this page suggests a LEFT function:

 

https://msdn.microsoft.com/en-us/library/ee634781.aspx

 

... but Power BI does not support this. It does however support a Text.Start function which accepts exactly the same parameters and does exactly the same thing.

 

This distinction seems to have something to do with the difference between DAX and "Power Query Language", but as much as I hate to be the one asking this kind of question, I can't find a clear explanation of these distinctions anywhere, and it's confusing things.

 

Can anybody shed any light on this?

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    OK, you seem to be confusing DAX and Power Query "M". DAX is not used in Power Query. Power Query is "M" code as defined here:

     

    https://msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US&f=255&MSPPError=-2147217396

     

    This is for the queries that get data into your data model. Think SSIS.

     

    Once your data is in your data model, then you use DAX to create new columns and measures.

     

    https://support.office.com/en-US/article/DAX-Function-Reference-DAX-6be048ab-a454-4a1d-bb37-e3d21bfb6a65

     

    Think MDX for SSAS.

     

    You wouldn't try using MDX in an SSIS package and similarly you wouldn't try to use DAX in Power Query "M".

     

    Hope this clears things up.

    • amien's avatar
      amien
      Icon for Helper V rankHelper V
      10 years ago

      Greg_Deckler

       

      Thanks for clarifing. If i can raise another question about this; 

       

      Is it best practice (performance, maintenance etc)  in PowerBI 2.0 to put all logic in Power Query (Data Model) or in DAX?

      For example : Sales and COGS are seperate columns in my data model. Do i pre-calculate this in the datamodel or create a DAX measure?

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Well, there are a number of opinions on that. The reality is that you will likely not be able to put all of your logic into Power Query because certain operations and logic will require related tables, etc. That being said, there are a number of professionals here and on Technet that have advocated for putting as much logic/operations into Power Query versus DAX. With that being said, I cannot say that I am 100% convinced that is the right answer as I can see pros and cons to both approaches. For example, if you already know that you are going to have to build some logic/operations in DAX, then it makes some sense to put it all in DAX since otherwise you are creating logic in two different languages and in two different places.

         

        Bottom line, I'm not sure there is a clear answer on this, people have their opinions but I haven't seen anything to convince me 100% one way or the other and with how the entire toolset is evolving so quickly, I don't think the "right" answer exists yet.