measuretable
1 Topic
Get related value to the max value of a measure column
Hey there! I'm currently displaying multiple columns, all calculated by measures, in a table visual: Product Name Total Sales Total Sales MoM Total Sessions Total Sessions MoM Product1 3000 50% 200 42% Product2 4500 -6% 200 10% Those measures are stored in a measure table. On a card visual for each MoM column, I want to display the MAX value of these columns and the respective "Product Name" as a description. I already got a solution for showing the maximum number, although I'm not sure if this is the leanest solution: MAXSalesByProductName = MAXX( KEEPFILTERS(VALUES('DimProduct'[Product Name])), CALCULATE('MeasureTable'[Sales_TotalSales MoM%]) ) I can't think of a solution to get the product name, as CALCULATE does not accept measures as filters and the filter function does not accept the MeasureTable as it is empty: First attempt with CALCULATE: VAR __MAX_SALES = 'MeasureTable'[MAXSalesByProductName] RETURN CALCULATE( FIRSTNONBLANK('DimProduct'[Product Name], 'DimProduct'[Product Name]), 'MeasureTable'[Sales_TotalSales MoM%] = __MAX_SALES ) Error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed. Second attempt with FILTER: VAR __MAX_SALES = 'MeasureTable'[MAXSalesByProductName] RETURN CALCULATE( FIRSTNONBLANK('DimProduct'[Product Name], 'DimProduct'[Product Name]), FILTER('MeasureTable', __MAX_SALES=[Sales_TotalSales MoM%]) ) Error: Table 'MeasureTable' cannot be used because it does not have any columns. So how to achieve this with this setup?Solved6.6KViews0likes2Comments