Measure : Select values that are not appearing in other table column

[YBZ]

Hi all,

I am stuck with a measure that should show the values that are not appearing in another table column. Below a sample :

Table1City

Paris

Barcelona

Rome

New York

Table2City

Paris

Barcelona

Rome

New York

Washington

I would now like to create a Matrix visual with the City Names that are in Table 2, but not in Table 1 (in this sample it should show Washington). Note that the real data consists of 1000 different city names.

Any idea?

Thanks

[Greg_Deckler · Accepted solution]

@YBZ Would need to know much more about relationships, filters, etc. In theory, you could create a measure like this:

Measure = CONCATENATEX(EXCEPT(SELECTCOLUMNS('Table2',"City",[City]),SELECTCOLUMNS('Table1',"City",[City])),[City],", ")

That would return them as a list. Alternatively, you could create a column in table 2 like this:

Column = 
  VAR __City = [City]
  VAR __Exists = COUNTROWS(FILTER('Table1',[City] = __City))
RETURN
  IF(__Exists = BLANK(),1,0)

[Greg_Deckler · Accepted solution]

@YBZ Would need to know much more about relationships, filters, etc. In theory, you could create a measure like this:

Measure = CONCATENATEX(EXCEPT(SELECTCOLUMNS('Table2',"City",[City]),SELECTCOLUMNS('Table1',"City",[City])),[City],", ")

That would return them as a list. Alternatively, you could create a column in table 2 like this:

Column = 
  VAR __City = [City]
  VAR __Exists = COUNTROWS(FILTER('Table1',[City] = __City))
RETURN
  IF(__Exists = BLANK(),1,0)

[YBZ]

thanks a lot @Greg_Deckler. I solved it with adding the column in table 2 as you proposed 🙂 

One question just for my understanding as the measure did not fully work as I have a lot of duplicate values in my columns. Therefore the result shows as well multiple times the same city name. 

When I wanted to add DISTINCT(), it created an error ... Not sure where to put to have only distinct values in my column. 

[Greg_Deckler · Accepted solution]

@YBZ That would be:

Measure = CONCATENATEX(DISTINCT(EXCEPT(SELECTCOLUMNS('Table2',"City",[City]),SELECTCOLUMNS('Table1',"City",[City]))),[City],", ")

[YBZ]

@Greg_Deckler  thanks a lot 🙂 Much appreciated!