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 want to use my measure in a filter context in a visual (shape map). The Table1 has a parent Table 2, the two tables are linked in a many to one relationship by a column "category".

 

I suspect there's something wrong with my lookup function, as I encounter the error "a table of multiple values was supplied"

 

Are there any ways to rewrite it ?

 

Thank you very much for your help,

 

 

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!

     

     

15 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

       

       

      • 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.