10-23-2017 12:35 PM - edited 10-23-2017 12:36 PM
Much as been written about qualifying all columns by the table name in dimensions, but not in measures when making these dax formulas, yet every single dax formula I make requires every single column to be qualified by the table. Like this one, all the columns here are from the same table. I can understand that the min function may require table qualification because it's in a different context, but as you can see pdate requires that same qualification. In that case, the general rule should just be qualify all your columns, because qualifying only dimension columns clearly doesnt' work as a rule.
By the way, the formula validator can't seem to find the table where pdate is, but the intellisense could when they proposed this syntax. Perhaps MSFT should fire the person that wrote the formula parser and move the guy who wrote the intellisense into that position, or at least have those two people meet and make friends. Of course, if they're both the same person, or if the parser and the intellisense functionality is using the same token table, that guy has mpd
10-23-2017 12:59 PM - edited 10-23-2017 06:53 PM
It is well documented best practice to ALWAYS qualify all column names in dax with a table name. I have covered it here, https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/ and in my book, as have countless others
your formula posted is not valid. If you are going to use MIN, you must also use FILTER.
10-23-2017 02:53 PM
Yes, I read your book first of all the power bi books I bought. Since I have your attention, I want to discuss with you the quality of your book. There are three major types of paper. There's just the normal paper you see every day, there's the pressed paper that gets a very smooth finish and there's the calendered paper which has a glossy sheen to it. Calendered paper is made by coating paper with a clay and pressing it to that glossy coffee resistant finish that really all computer books should have. Your book is very nice, but unlike the power pivot and power bi book by collie, it doesn't have that same coffee resistance. It seems like it would with the finish and all, but it doesn't. I don't know why but you should take this matter up with your publishers.
Second, if you read page 56 of the collie book, it says right there: to reference a measure we omit the table name. Also, if you watch the Ferrari video https://www.sqlbi.com/tv/advanced-dax/, he explicitly says he refuses to touch a model where the measure convention with the table names are not followed. I thought about this for awhile now. You may have misinterpreted his sentance and thought he meant the return values of an aggregate function are measures, and not the measures in a fact table, but of course this can not be the interpretation. After all, what would be the proper table for an aggregate function that spans two tables. In sql I'd write it as: select sum(if(a, t.a, t.b)) from a join b on something. I don't know how to write it in dax, but I"m sure it must exist when you do FvA analysis (aka pacing, variance, mda analysis)
10-23-2017 07:02 PM
Thanks for the feedback on the book. I will pass it on.
Regarding the DAX, I am pretty sure I am not confused, but happy to be corrected if I am wrong.
the general rule should just be qualify all your columns, because qualifying only dimension columns clearly doesnt' work as a rule.
This is the rule (best practice) = always qualify all columns. There is no rule that I am aware of that says "only qualify dimension columns". Where did you get this rule from?
Second, if you read page 56 of the collie book, it says right there: to reference a measure we omit the table name
That is exactly what I said in my blog article linked above.
if you watch the Ferrari video https://www.sqlbi.com/tv/advanced-dax/, he explicitly says he refuses to touch a model where the measure convention with the table names are not followed.
I totally agree and this is entirely consistent with what I said earlier and in my blog post and in my book.
I think you are confusing 2 concepts (maybe I am wrong, not sure, but this is how I am interpreting what you are saying).
Concept 1. Naming conventions.
Columns should always be named TableName[Column Name] Always, always, always - no exceptions.
Measures should always be named [Measure Name] excluding the table name, always, always always, - no exceptions.
Concept 2. Refering to Column Names inside a Measure Formula.
This is a totally different thing. If you are refering to a column name INSIDE a measure formula, you should always qualify the column name as TableName[Column Name].
Have I misunderstood what you are saying?
Your formula doesn't work because you need to use the FILTER fuction as I showed above.
10-24-2017 07:28 AM
Yes I got the formula to work, it was an early in the process when I took the picture, I just wanted to show the intellisense.
This is where we differ. The definition of measure is one of two things
1. The conventional definition:
so, if you did sum(amt) in a fact table that has amt as a column, the measure is the column amt, and it can be aggregated with summed average etc.
2. Probably your definition, not sure if it's everyone elses, but if it is, suddently everything makes sense since the rest of DAX doesn't follow typical programming convention.
Measures are the executed aggregate function such as sum(amt) returning an aggregate value?
The whole dax language does not use conventional terminology so it may be that "measures" is used differenlty here than in the rest of the world. Microsoft did this to ie6 so if everyone drank the koolaid, I guess 2 may be right. If DAX spelled out its data types in their documentation like every other language in the world does in their documentation, this wouldn't be such a hard medium to communicate with others. I don't mean like primitives. I mean like these "measures". For example the documentation for calculate is
But that's clearly not the case because in every other language, https://en.wikipedia.org/wiki/Expression_%28computer_science%29
x=5 is an expression. What really belongs in <expression> is what the #2 version of the definition for the word "measure" actually means, if indeed I'm interpretting your definition of measure correctly. This is like learning french, except the words are exactly the same as english. It's just a different meaning, like a Rene Magritte fan trying to prove a point in computer languages.