Forum Discussion

Anonymous_226's avatar
Anonymous_226
Frequent Visitor
3 years ago
Solved

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 ...
"DAX"
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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's avatar
Anonymous_226
Frequent Visitor
3 years ago

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's avatar
andhiii079845
Solution Sage
3 years ago

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.