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!
It seems like you are looking up values from the same table, correct? (all columns refer to table 1)
Can you show a depiction of your model?
Hello Paul,
Yes indeed, all columns refer to the table RegNuance (Table 1). Here are my model and the first rows of this table.
Then I go to the conditionnal formatting of my shape map. I encounter no problem with the coloration of the map, but I have issues using the slicer of the category RegNuance[Code Nuance].
Thanks
model
RegNuance
coloration of the map
- Anonymous4 years agoNot applicable
Hi everyone,
To sum to, I want to :
- Retrieve in a measure the color code RegNuance[Couleur Nuance] corresponding to the maximum of votes Max Voix = MAX('RegNuance'[Voix])
- Use this measure to color the shape map in the filtering context (division by region) ex. [code de la région]
Is it the good way to proceed or am I wrong ?
I can upload my file if needed.
- PaulDBrown4 years agoCommunity Champion
Please share sample data or a mock PBIX file
- Anonymous4 years agoNot applicable
Hello Paul,
Here is a dropbox link of the .pbix election_map . No problem, the data is public (French gouv).
When I choose "NUP" for example in the slicer, I get the error on the shape map "A table of multiple values was supplied where a single value was expected".
Thanks