Forum Discussion
Comparing mutliples tables and returing unique values
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,
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.
10 Replies
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 () )
)
)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?
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.
Try this:
Measure =
VAR __table1 = CALCULATE(countrows(TableA),RELATEDTABLE(TableB),Filter(TableA,TableA[Fruit]<>"Kiwi"),Filter(TableB,TableB[Order]=BLANK()))
Return __table1
The result: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 __table1This 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.
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.
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"
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.
