Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to Solution.
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
Proud to be a Super User!
Paul on Linkedin.
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!
Proud to be a Super User!
Paul on Linkedin.
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?
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
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
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
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).
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
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)
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.
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
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!
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
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.
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.