Question on Relatedtable dax function usage

[han_rj]

Please may I have help understand how the relatedtable function on the sales table is cutting down filters of Category and Subcategory for this average measure.

In my understanding relatedtable fuction returns a related table. But need help understand how the related table helps surpass the category and subcategory filters

This is my Model, I have two tables sales and product connected through Product key 

Sample Data

Products Table

Sales table

[DataNinja777 · Accepted solution]

Hi @han_rj ,

 The measure works by combining the ALL function and RELATEDTABLE function to calculate an average across all unfiltered combinations of Category and Subcategory in the Product table. The ALL function removes any filters that might be applied to the Category and Subcategory columns, ensuring that the calculation considers every possible combination, regardless of slicers or report filters.

The RELATEDTABLE function plays a key role in retrieving rows from the Sales table that are related to the current row being iterated in the Product table. When AVERAGEX iterates over all combinations of Category and Subcategory, RELATEDTABLE ensures that only the rows in the Sales table that correspond to the currently iterated Product are included in the calculation.

Together, these functions allow the measure to bypass any filters on Category and Subcategory while still ensuring that the relationship between the Product and Sales tables is maintained. This enables the calculation to sum the Quantity and Net Price for each relevant Sales row and then average the results across all combinations of Category and Subcategory. This approach ensures that the average is calculated correctly, taking into account all unfiltered combinations of categories and subcategories while focusing on the relevant sales data for each combination.

Best regards,

[dharmendars007]

Hello @han_rj , 

In your scenario, the use of the RELATEDTABLE function helps bridge the relationship between the Sales table and the Products table. This bridging behavior influences how filters applied to one table impact the other in the context of DAX measures.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN