Hi,
Are you able to please advice which training modules have examples of related and relatedtable in measures. I am trying to gain a clear understanding of how the functions calculate.
Cheers
Elizabeth
Elizabeth,
Give me a bit and I’ll try to locate something for you.
Guy
Elizabeth,
Give this a look for the RELATED function
http://portal.enterprisedna.co/courses/108877/lectures/2000622
Guy
1 Like
Elizabeth,
Her is some more from the DAX Guide I use
The RELATEDTETABLE function performs a context transition from row context(s) to a filter context, and evaluates the expression in the resulting filter context.
This function is a shortcut for CALCULATETABLE function with no additional filters, accepting only a table reference and not a table expression.
The RELATED function requires that a relationship exists between the current table and the table with related information. The argument specifies a column reference, and the function follows a chain of one or more many-to-one relationships to fetch the value from the specified column in the related table. If a relationship does not exist, RELATED raises an error.
The RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function. A table scanning function, such as SUMX, gets the value of the current row value and then scans another table for instances of that value.
The DAX Guide is at: https://dax.guide/
Hope this helps.
Guy
Hi Guy,
Re Relatedtable - Must an active relationship exist between the two columns in the model.
Elizabeth
Elizabeth,
It’s not required
Guy
Elizabeth,
Nothing by Sam on RELATEDTABLE so try this
Guy
Hi Guy,
Thank you for making things clearer. I worked out why the relatedtable function was not calculating in the date table. Date column was not set to date data type. That only took me 2 days to work out. lol
Cheers
Elizabeth Tachjian
Elizabeth,
Been there, done that.
Guy
I find her videos to confusing. I prefer your explanation.
Can I ask to clarify my understanding …based on the below rule, I can use Filter function in the related function.
The RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function
Hi Guy,
Why use Relatedtable when we can just use Calculatetable. I am not clear when it is best to use one over the other.
All 3 calculations below produce the same result. All are in the Date table. I understand CALCULATE will not return a table whereas CALCULATETABLE and RELATEDTABLE will.
Calculatetable = COUNTROWS(CALCULATETABLE(sale))
Relatedtable Countrows Sales # = COUNTROWS(RELATEDTABLE(Sale) )
CalculateCountrows = CALCULATE(COUNTROWS(Sale))
Cheers
Elizabeth
Elizabeth,
These two DAX Functions are the same
CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
CALCULATETABLE function is a synonym for the RELATEDTABLE function.
DAX RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify.
DAX RELATEDTABLE function is equivalent to DAX CALCULATETABLE function with no logical expression.
Guy