Hi again @Anonymous
I've had a further look and believe I've come up with a version of Over rank that works.
The reason it's a little bit complicated is that we have to mimic the behaviour of SUMMARIZECOLUMNS (the function that Power BI uses within the DAX query generated by the visual), without using SUMMARIZECOLUMNS ourselves. SUMMARIZECOLUMNS does not support evaluation within a context transition, which we would need if we were to use it in this measure.
The measure instead uses GENERATECROSSJOIN/SUMMARIZE to construct a table whose rows correspond to the rows of the table visual (including removing rows where all measures are blank). Also, in order to mimic Auto-exist behaviour, we can't allow 'Date Table' filters to filter 'Final Table' when deciding which combinations of values from 'Final Table' are included.
The table is constructed by CROSSJOINing distinct combinations from 'Final Table' (ignoring the 'Date Table' filters) and 'Date Table', then removing rows that have all-blank measures. The rank is then calculated using this table as the first argument of RANKX.
I put some comments in the code that hopefully make it clearer. Also, I have written a simpler version of the measure that I think will work just as well. The simpler version assumes that [R3M Error] will be nonblank in every row of the table visual.
Full version:
Over rank =
VAR RankingTable =
CALCULATETABLE (
GENERATE (
-- The CROSSJOIN (...) code produces the set of rows that would be included in the visual
-- if no measures were included (i.e. before removing rows with all measures blank.
CROSSJOIN (
-- Summarize columns of 'Final Table'.
-- Filters on 'Date Table' are removed using REMOVEFILTERS, to mimic Auto-exist behaviour:
-- Auto-exist means the values from 'Final Table' are not filtered by 'Date Table'
-- for the purpose of determining combinations to appear in the visual.
CALCULATETABLE (
SUMMARIZE (
'Final Table',
'Final Table'[Account Group],
'Final Table'[Salesperson],
'Final Table'[Region/Forecasting Entity],
'Final Table'[Warehouse],
'Final Table'[Product Name]
),
REMOVEFILTERS ( 'Date Table' )
),
-- Summarize columns of 'Date Table'.
-- Since 'Final Table' doesn't filter 'Date Table', we don't need REMOVEFILTERS here.
SUMMARIZE (
'Date Table',
'Date Table'[Month Year],
'Date Table'[Year Month Number]
)
),
-- Evaluate all measures included in the visual.
-- This could be reduced to a minimal set of measures.
-- In sample data model, [R3M Error] could be sufficient.
VAR Measure_Error = [Error]
VAR Measure_Forecast_Requested = [Forecast Requested]
VAR Measure_Requested = [Requested]
VAR Measure_LM_Forecast = [LM Forecast]
VAR Measure_LM_Requested = [LM Requested]
VAR Measure_2PM_Forecast = [2PM Forecast]
VAR Measure_2PM_Requested = [2PM Requested]
VAR Measure_R3M_Forecast = [R3M Forecast]
VAR Measure_R3M_Requested = [R3M Requested]
VAR Measure_R3M_Error = [R3M Error]
VAR Include_Row =
-- Only include rows where at least one measure is nonblank
-- (i.e. where not all measures are blank)
NOT (
ISBLANK ( Measure_Error ) && ISBLANK ( Measure_Forecast_Requested )
&& ISBLANK ( Measure_Requested )
&& ISBLANK ( Measure_LM_Forecast )
&& ISBLANK ( Measure_LM_Requested )
&& ISBLANK ( Measure_2PM_Forecast )
&& ISBLANK ( Measure_2PM_Requested )
&& ISBLANK ( Measure_R3M_Forecast )
&& ISBLANK ( Measure_R3M_Requested )
&& ISBLANK ( Measure_R3M_Error )
)
RETURN
-- Add the [R3M Measure] value to the table, but exclude the row if all measures are blank.
FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
),
-- Use ALLSELECTED to ensure RankingTable is constructed in the overall filter context of the visual
ALLSELECTED ()
)
RETURN
-- Finally, return the rank of [R3M Error] in current filter context, compared with all values in RankingTable
RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )
Simplified version:
Over rank =
VAR RankingTable =
CALCULATETABLE (
GENERATE (
CROSSJOIN (
CALCULATETABLE (
SUMMARIZE (
'Final Table',
'Final Table'[Account Group],
'Final Table'[Salesperson],
'Final Table'[Region/Forecasting Entity],
'Final Table'[Warehouse],
'Final Table'[Product Name]
),
REMOVEFILTERS ( 'Date Table' )
),
SUMMARIZE (
'Date Table',
'Date Table'[Month Year],
'Date Table'[Year Month Number]
)
),
VAR Measure_R3M_Error = [R3M Error]
VAR Include_Row = NOT ISBLANK ( Measure_R3M_Error )
RETURN
FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
),
ALLSELECTED ()
)
RETURN
RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )
Kind regards,
Owen
