RELATEDTABLE

[Drikus]

Hi I'm working my way through The Definitive Guide to DAX.

In the book the below code is used to determine the top selling products. The code below makes sense to me at a high level.

I split out the code in separate queries or measures to see how it all works in Power BI.

I first created a new table using the following code:

Subcategories =

    ALL ( 'Product'[Category], 'Product'[Subcategory] )

I then added a measure to get the average sales, however it does not seem that this takes into account the filter context when putting this in a table in PowerBI as for each product and category the totals are exactly the same and add up to the total value of all the sales.

The description on Microsoft's website for RELATEDTABLE are the following "The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify."

If this is the case why is my measure not taking into account the filter context? What am I doing wrong?

AverageSales =

    AVERAGEX (

        Subcategories,

        SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )

    )

Code in Definitive Guide to DAX down below:

BestCategories =

VAR Subcategories =

    ALL ( 'Product'[Category], 'Product'[Subcategory] )

VAR AverageSales =

    AVERAGEX (

        Subcategories,

        SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )

    )

VAR TopCategories =

    FILTER (

        Subcategories,

        VAR SalesOfCategory =

            SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )

        RETURN

            SalesOfCategory >= AverageSales * 2

    )

RETURN

    TopCategories

The file I'm using is shared on Google Drive below.
(No sensitive information was included, all information is publically available) 

https://drive.google.com/file/d/1iYBBnAZZfwNgJtVkgDgkGFgmdHIHRiqk/view?usp=drivesdk

[Drikus]

@tamerj1 
Thanks for looking at this.

The "BestCategories" table is the solution provided in the book. I noticed that this is also does not have a relationship with the sales table, but overall is filtering for the correct top items (Items with a sales value 2 time that of the average sales). 

In the table that I've created "Catefory & Subcategories" I merely split out the solution into individual steps by creating a new Table "Category & Subcategory = ALL ( 'Product'[Category], 'Product'[Subcategory] )" and then adding the measure:
"AverageSales = AVERAGEX (

'Category & Subcategory',

SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )

)

Also when I tried adding relationships I got a notification :"A circular dependancy was detected". I assume its bringing the relationship in from the "Product" table.

Why would the the "BestCategories" calculate the correct amounts if it does not have any relationship?

Thanks so much

[tamerj1]

@Drikus 
The subcategory table is completely disconnected. You did not set any relationship. Sales amounts from sales table cannot be filtered.

[CNENFRNL]

Subcategory keeps data lineage referencing to the original Product table.

[tamerj1]

@CNENFRNL 

Please have a look at the file. The Top Categories table is calculated using the Product table lineage. However, the question is why the measures (sales amount) that are calculated based on the subcategory table are not filtered by category/subcategory. And the answer is because this table has no relationship with the sales table. 

[CNENFRNL]

My bad, I thought the PO was talking about the derived table Subcategories on the fly. As to that materialized caculated table, I totally agree with you, only relationships matter.