Forum Discussion
DAX Functions - Level of Support
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.
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.
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.
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.
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?
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.
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.
Makes perfect sense now, thanks!
