“A table of multiple values was supplied where a single value was expected”
is a common error in DAX especially when looking up a value from another TABLE where duplicates exist
Lets take a small example. Following is the Table “Players_Table” of Top 15 Tennis Players by Points
| Country | Player | Age | Points |
| ESP | Rafael Nadal | 31 | 10,465 |
| ESP | Pablo Carreno Busta | 26 | 2,650 |
| ESP | Roberto Bautista Agut | 29 | 1,935 |
| FRA | Jo-Wilfried Tsonga | 32 | 2,490 |
| GBR | Andy Murray | 30 | 5,290 |
| GBR | Aljaz Bedene | 28 | 993 |
| GBR | Kyle Edmund | 22 | 807 |
| GER | Alexander Zverev | 20 | 4,400 |
| GER | Mischa Zverev | 30 | 1,457 |
| GER | Philipp Kohlschreiber | 34 | 1,295 |
| SUI | Roger Federer | 36 | 8,505 |
| SUI | Stan Wawrinka | 32 | 4,000 |
| USA | John Isner | 32 | 2,550 |
| USA | Sam Querrey | 30 | 2,550 |
| USA | Jack Sock | 25 | 1,900 |
We have another Table “Country_Table” of selected Countries. Our objective is to Lookup the Best Player for each of the countries in this TABLE
i.e.
| Country | Best Player |
| ESP | |
| SUI | |
| GER | |
| UK | |
| USA |
One way to solve this is to Add a Rank Column in the Players_Table which will Rank the Players by Countries
RANK = RANKX ( FILTER ( ALL ( Players_Table ), Players_Table[Country] = EARLIER ( Players_Table[Country] ) ), Players_Table[Points], , DESC, DENSE )
This will give us
Now we can get the best player in the Country_Table using DAX function LookUpvalue or a combination of Calculate,VALUES and FILTER
i.e.
Best_Player = LOOKUPVALUE ( Players_Table[Player], Players_Table[Country], Country_Table[Country], Players_Table[RANK], 1 )
or
Best Player = CALCULATE ( VALUES ( Players_Table[Player] ), FILTER ( ALL ( Players_Table ), Players_Table[Country] = Country_Table[Country] && Players_Table[RANK] = 1 ) )
The result of this calculated column is an error… WHY? Just because in USA….John Isner and Sam Querry have same points and both share RANK 1 for USA
If you just increase the points of Sam Querry by only 1 in the Players_Table, the error will disappear once you refresh your data. i.e.
DEALING WITH THE DUPLICATES
In the practical world there are many such cases where you have to deal with the Duplicates. Following are two handy approaches to take in such situation
1) Use CONCATENATEX to get all duplicates as RESULT
The formula used to get above Results is
Best Player = CONCATENATEX ( FILTER ( ALL ( Players_Table ), Players_Table[Country] = Country_Table[Country] && Players_Table[RANK] = 1 ), Players_Table[Player], " , " )
2) Use FIRSTNONBLANK / LASTNONBLANK to get one of the many duplicates as RESULT
FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank.
Thus we can use these formula to return either Sam Querry or John Isner as the best player of USA.
Best Player (FirstNonBlank) = CALCULATE ( FIRSTNONBLANK ( Players_Table[Player], 1 ), FILTER ( ALL ( Players_Table ), Players_Table[Country] = Country_Table[Country] && Players_Table[RANK] = 1 ) )
Best Player (LastNonBlank) = CALCULATE ( LASTNONBLANK ( Players_Table[Player], 1 ), FILTER ( ALL ( Players_Table ), Players_Table[Country] = Country_Table[Country] && Players_Table[RANK] = 1 ) )
The results are as follows
MULTIPLE SPARKLINES
This is my new Custom Visual “Multiple Sparklines“. Please check it out and let me have your feedback and suggestions
