DAX Functions - Level of Support

[tomhawley]

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?

[Greg_Deckler · Accepted solution]

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

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.

[tomhawley]

Makes perfect sense now, thanks!

[amien]

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

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.

[amien]

I worked with QlikView for 5 years and in that enviroment the golden rule is to put as much logic in "Power Query" and only do measures if not otherwise possible. Cause i was learned to do this, seems like i'm doing this also in PowerBI. Not sure if this is correct or not. Hope Microsoft will come with some kind of white paper on this topic.