Dealing with Duplicates “A Table of Multiple Values was supplied” using DAX

“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

Share This:

Leave a Reply

Your email address will not be published. Required fields are marked *