cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ffitzpatrick47 Regular Visitor
Regular Visitor

table qualified columns

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. 

image.png

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

4 REPLIES 4
Super User
Super User

Re: table qualified columns

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. 

 

=calculate([measure],filter(table,tablename[column] =min(table[othercolumn])))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Ffitzpatrick47 Regular Visitor
Regular Visitor

Re: table qualified columns

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)

Highlighted
Super User
Super User

Re: table qualified columns

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.








@Ffitzpatrick47 wrote:

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?

 








@Ffitzpatrick47 wrote:

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.

 

 








@Ffitzpatrick47 wrote:
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.

 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Ffitzpatrick47 Regular Visitor
Regular Visitor

Re: table qualified columns

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:

image.png

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 

CALCULATE(<expression>,<filter1>,<filter2>…)  

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.