Comparing mutliples tables and returing unique values

[Anonymous_226]

Hi,

I have a requirement for the following to be done in DAX. I cannot create and additonal columns as I am connected with direct query to a dataset.

I have a scenario with 2 seperate tables that are related by a number column.

Table A
Number | Fruit
1            | Apple

2            | Orange

3            | Strawberry

4            | Grape

5            | Plum

1            | Kiwi

2            | Kiwi

Table B

Order     | Number
               | 1

               | 2

00367     | 3

00485     | 4

00059     | 5

00689     | 6

00745     | 8

Here is what I am looking to accomplish:
1. The first thing I need to do is take Table A and remove any row that has the Kiwi in the fruit column.

2. I need to take the values in the number column of Table A and find wich of those are not present in the number column in Table B

Thanks,

[v-zhangti · Accepted solution]

Hi, @Anonymous_226 

You can try the following methods.
New Table:

Table = FILTER('Table A',[Fruit]<>"Kiwi")

Table B:

Measure = 
Var _table=EXCEPT(VALUES('Table'[Number]),VALUES('Table B'[Number]))
Return
IF(SELECTEDVALUE('Table'[Number]) in _table,1,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous_226]

@andhiii079845that makes a bit more sense then. TableA has a 1:n relationship to TableB so i would be looking from the "1" to the "n"

[tamerj1]

Hi @Anonymous_226 

please place the following filter measure in the filter pane of the table visual, select "is not blank" and apply the filter 

FilterMeasure =
COUNTROWS (
FILTER (
TableA,
TableA[Fruit] <> "Kiwi"
&& TableA[Number]
IN CALCULATETABLE ( VALUES ( TableB[Number] ), TableB[Order] = BLANK () )
)
)

[Anonymous_226]

@tamerj1Thank you for that. What I am seeing is that gives me all of the numbers from TableA that are present in TableB. However in the comments below I am needing to get all the numbers in TableA that are not in TableB. I cant see to correct the measure properly to achieve that. Do you have any suggestions?

[tamerj1]

@Anonymous_226 

Seems to working. I even tried a 2nd option. See below and refer to attached sample file. Please provide sample data that accurately simulates you real one.

[Anonymous_226]

@tamerj1in my relationship TableA a 1:* with TableB. Would that make a difference? When I try your "FilterMeasure 2" i get an error saying there is no relationship available from TableB[order]

[tamerj1]

@Anonymous_226 

As per the sample data, TableA is the many and TableB is the one not the opposite. Again please double check and provide correct sample data. 

[andhiii079845]

Try this:

Measure = 
VAR __table1 = CALCULATE(countrows(TableA),RELATEDTABLE(TableB),Filter(TableA,TableA[Fruit]<>"Kiwi"),Filter(TableB,TableB[Order]=BLANK()))
Return __table1

The result:

[Anonymous_226]

@andhiii079845Thank you for that. That helped get me in the right direction. The one thing I am noticing now that I am taking another look at it is that I actually need to return the numbers that are not in Table B.

I have part of the measure you provided me:

Measure = 
VAR __table1 = CALCULATE(countrows(TableA),RELATEDTABLE(TableB),Filter(TableA,TableA[Fruit]<>"Kiwi")))
Return __table1

This gives me the numbers from TableA that are also in TableB which is what I had orginally thought I needed but now I am looking at the data and I need the numbers from TableA that are not in TableB. I seem to be having trouble modifying that measure properly. Any suggestions?

Thank you for the help.

[andhiii079845]

If you change the "direction" of the search / look up, you need to change relatedtable to related. Both depands of the relationship: 1:n. Do you look from "1" to the "n" table or do you look from "n" to the "1" table.