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!

     

     

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,

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.