Forum Discussion
alternative to lookupvalue function
- 4 years ago
My apologies. I should have looked at the structure of the map. You need to change the model slightly and consequently the measure:
Couleur Max Region New = VAR _MXVoix = CALCULATE ( MAX ( 'RegNuance'[Voix] ), ALLEXCEPT ( RegNuance, Reg[Région] ) ) VAR _Colour = CALCULATE ( MAX ( RegNuance[Couleur Nuance] ), FILTER ( ALLEXCEPT ( RegNuance, Reg[Région] ), RegNuance[Voix] = _MXVoix ) ) RETURN _Colour - 4 years ago
Sure. You need a couple of measures:
Sum Voix = SUM(RegNuance[Voix])Max Voix by region = CALCULATE(MAX('RegNuance'[Voix]), ALLEXCEPT(RegNuance, Reg[Région]))Filter Map by Winning Code Nuance = VAR _ValVoix = SUMMARIZE ( RegNuance, Reg[Région], Nuance[Code Nuance], "@SUM", [Sum Voix] ) VAR _MAXVoix = ADDCOLUMNS ( SUMMARIZE ( RegNuance, Reg[Région], Nuance[Code Nuance] ), "@SUM", [Max Voix by region] ) VAR _filter = CALCULATETABLE ( VALUES ( Nuance[Code Nuance] ), INTERSECT ( _ValVoix, _MAXVoix ) ) RETURN COUNTROWS ( _filter )Add this last measure [Filter Map by Winning Code Nuance] to the map's filter in the filter pane and set the value to greater or equal to 1
To get:
and a bonus measure to return the winning Nuance Code by region for the maps tooltip:
Code Nuance with max votes by region = VAR _ValVoix = SUMMARIZE ( RegNuance, Reg[Région], Nuance[Code Nuance], "@SUM", [Sum Voix] ) VAR _MAXVoix = ADDCOLUMNS ( SUMMARIZE ( RegNuance, Reg[Région], Nuance[Code Nuance] ), "@SUM", [Max Voix by region] ) VAR _filter = CALCULATETABLE ( VALUES ( Nuance[Code Nuance] ), INTERSECT ( _ValVoix, _MAXVoix ) ) RETURN CONCATENATEX ( _filter, 'Nuance'[Code Nuance], ", " )Hope this helps!
Thank you very much for your kind help Paul, it seems to work ! I would not have thought to use MAX of a non-numeric column. I will have to think it over 🙂
Cheers
the MAX expression in the second VAR is determined by the filter context defined by:
FILTER ( ALLEXCEPT ( RegNuance, Reg[Région] ), RegNuance[Voix] = _MXVoix )
which filters for rows where the RegNuance[Voix] = the max Voix value for each region
The MAX calculation "kicks in" (always) if there were two identical RegNuance[Voix] values with different Colours for the same region. The measure would in that case select the max value for the colour (which could hypothetically happen). The max colour value would be the top row by region if you were to sort the table by region and colour in descending order (for colour)