Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

alternative to lookupvalue function

Hi everyone,   I'm trying to look up values using this sample formula in a measure : Max Category = LOOKUPVALUE (Table1[Col_to_lookup], Table1[col_num], Table1[Max(Table1[col_num])])   Then, I w...
Need Help
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    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

     

     

  • PaulDBrown's avatar
    PaulDBrown
    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!

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello Paul,

Thank you for your explanation Paul, there could me two maxima for RegNuance[Voix] indeed.

Is it possible to dynamically change the map according to the winner party also ?

At that point, with no filter, if I select a party with the slicer, nothing chances in the map.  What I'm aiming for is to only make appear the regions where the party (ies) seclected won.

slicer [Code Nuance] : "NUP"

Thanks

PaulDBrown's avatar
PaulDBrown
Community Champion
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!

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Paul,

    Indeed, putting the measure [Sum Voix] for the colour saturation also works.

    I Thank you for the time you dedicated on my topic, it really helped me and I learnt a lot.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    If you want the maximum votes by region as a column, I recomend using this code (instead of the one you have which references a measure):

    New Voix Gagnant by region =
VAR _MX =
    CALCULATE (
        MAX ( RegNuance[Voix] ),
        FILTER (
            RegNuance,
            RegNuance[Code de la région] = EARLIER ( RegNuance[Code de la région] )
        )
    )
RETURN
    IF ( RegNuance[Voix] = _MX, RegNuance[Voix] )

    If more than one row has the same number of votes, the code will retun the corresponding value for all the affected rows.
    Just beware that best practices recommend avoiding calculated columns were possible.

    BTW I  tried including the [sum  voix] measure for the colour saturation and it also works, probably because you included the Code Nuance as the legend and set each colour individually.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Paul,

    Many thanks for your detailed post, it works well 🙂 !

    I am far away of understanding the two measures [Filter Map by Winning Code Nuance] and [Code Nuance with max votes by region] you have created. I will have to take time, going step by step.

    I eventually found another way to get the winner by region.

    For that, I needed to create a calculated column in RegNuance :

     

    Nombre de Voix Gagnant Region = 
VAR _MaxRegion =
CALCULATE ([Max Voix by region]
        )
RETURN
IF(RegNuance[Voix]=_MaxRegion,RegNuance[Voix])

     

    And then, I add that calculated column in the field color saturation of the shape map. It seems to work.

    The only thing is that I will have to deal with the case there are more than one maximum (in RegNuance[Voix]), maybe it could bring me some problems. Is there a simple way to do this in a calculated column ?

    My file up-dated,  with the solutions in the two pages : election_map_sol 

     
    Regards