Using RELATED() in measure when there is no relationship

[ajit_singh]

The example is from the book "The Definitive Guide to DAX - 2nd Ed - Alberto Ferrari, Marco Russo", Chapter 15, example 15-5.

The table "Advertised Brands" table has not relationship with Product or Date.

From the definition of RELATED() at https://dax.guide/related/

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.

How RELATED() works in below measure when there is no relationship?

Advertised Brand Sales = 
SUMX (
    FILTER ( 
        Sales,
        CONTAINS ( 
            'Advertised Brands',
            'Advertised Brands'[Brand], RELATED ( 'Product'[Brand] ),
            'Advertised Brands'[Calendar Year], RELATED ( 'Date'[Calendar Year] ),
            'Advertised Brands'[Month], RELATED( 'Date'[Month] )
        )
    ),
    Sales[Quantity] * Sales[Net Price]
)

And measure gets data for each brand where the [Sales Amount] measure doesn't get for each brand?

 

[wdx223_Daniel · Accepted solution]

the row context of Function RELATED comes form FILTER's 1st parameter, i.e. table of Sales, which has a many to one relationship with table Dates

[ajit_singh]

Crisp, concise and clear, thank you 🙏