cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pierodelaluna
Frequent Visitor

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,

 

 

2 ACCEPTED SOLUTIONS

My apologies. I should have looked at the structure of the map. You need to change the model slightly and consequently the measure:
model.png

 

 

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

 

Map colours.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

map filter.pngTo get:

 

Map filter.gif

 

 

 

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], ", " )

 

2022-07-05.png

 

Hope this helps!

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

15 REPLIES 15
PaulDBrown
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

modelmodel

 

RegNuanceRegNuance

 

coloration of the mapcoloration of the map

 

 

 

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.

Please share sample data or a mock PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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

result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 Newwith Couleur Max Region New

My apologies. I should have looked at the structure of the map. You need to change the model slightly and consequently the measure:
model.png

 

 

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

 

Map colours.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you very much for your kind help Paul, it seems to work ! I would not have thought to use MAX of a non-numeric column. I will have to think it over 🙂

Cheers

the MAX expression in the second VAR is determined by the filter context defined by:

     FILTER ( ALLEXCEPT ( RegNuance, Reg[Région] ), RegNuance[Voix] = _MXVoix )

which filters for rows where the RegNuance[Voix] = the max Voix value for each region

The MAX calculation "kicks in" (always) if there were two identical RegNuance[Voix] values with different Colours for the same region. The measure would in that case select the max value for the colour (which could hypothetically happen). The max colour value would be the top row by region if you were to sort the table by region  and colour in descending order (for colour)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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"slicer [Code Nuance] : "NUP"

Thanks

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

map filter.pngTo get:

 

Map filter.gif

 

 

 

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], ", " )

 

2022-07-05.png

 

Hope this helps!

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors