topic Re: Comparing mutliples tables and returing unique values in DAX Commands and Tips

Comparing mutliples tables and returing unique values

Anonymous_226 — Thu, 02 Mar 2023 20:25:09 GMT

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,

Re: Comparing mutliples tables and returing unique values

andhiii079845 — Wed, 01 Mar 2023 06:53:57 GMT

Try this:

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

The result:

Re: Comparing mutliples tables and returing unique values

Anonymous_226 — Wed, 01 Mar 2023 14:46:33 GMT

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.

Re: Comparing mutliples tables and returing unique values

andhiii079845 — Wed, 01 Mar 2023 17:12:53 GMT

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.

Re: Comparing mutliples tables and returing unique values

tamerj1 — Wed, 01 Mar 2023 18:18:17 GMT

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 () )
)
)

Re: Comparing mutliples tables and returing unique values

Anonymous_226 — Wed, 01 Mar 2023 19:15:20 GMT

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"

Re: Comparing mutliples tables and returing unique values

Anonymous_226 — Thu, 02 Mar 2023 16:54:16 GMT

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?

Re: Comparing mutliples tables and returing unique values

tamerj1 — Thu, 02 Mar 2023 18:50:48 GMT

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.

Re: Comparing mutliples tables and returing unique values

Anonymous_226 — Thu, 02 Mar 2023 20:13:25 GMT

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]

Re: Comparing mutliples tables and returing unique values

tamerj1 — Fri, 03 Mar 2023 03:37:38 GMT

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.

Re: Comparing mutliples tables and returing unique values

v-zhangti — Thu, 09 Mar 2023 08:05:40 GMT

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.