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

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.

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Please share sample data or a mock PBIX file

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      See if this measure works:

      Couleur Max Region New =
VAR _MXVoix =
    CALCULATE (
        MAX ( 'RegNuance'[Voix] ),
        ALLEXCEPT ( RegNuance, RegNuance[Code de la région] )
    )
VAR _Colour =
    CALCULATE (
        MAX ( RegNuance[Couleur Nuance] ),
        FILTER (
            ALLEXCEPT ( RegNuance, General[Code de la région] ),
            RegNuance[Voix] = _MXVoix
        )
    )
RETURN
    _Colour

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Paul, the measure looks good, seeing the column RegNuance[Couleur Max Region New]

         

        However, when I enter the formula in the conditionnal formatting of the shape map, BI doesn't take account the regions, giving the same color (ie red, max of the country).

         

        with Couleur Max Region New