Forum Discussion
Question on Relatedtable dax function usage
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,
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,