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

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

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