Forum Discussion

kasife's avatar
kasife
Icon for Helper V rankHelper V
3 years ago
Solved

From to between fact tables

Hello people,

I would like some help perhaps with modeling, I can't find a solution

I would like to make a comparison from one table to another. For example,

- table A: ID 1, ID 2, ID 3, ID 4, ID 5
- table B: ID 1, ID 2

Result : (3)    ID 3, ID 4, ID 5 

Would I have to do this through some modeling or could I do it via DAX? How could I get this result?

My modeling

 

Need Help
  • kasife's avatar
    kasife
    3 years ago

    parry2k II think there was some detail missing that maybe I forgot because the value wasn't matching, so I used the following measurement and it worked. 

    COUNTROWS(FILTER(_ccvs, NOT tableA[id] IN VALUES(tableA[id])))

    Thanks so much

     

8 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    kasife it is easy to do but how do you want to visualize the data or do you want to create a new table with the exception?

    • kasife's avatar
      kasife
      Icon for Helper V rankHelper V
      3 years ago

      parry2k  I want to count and see which IDs I have in table A that I don't have in table B. I tried to show in a visual way the result I would like to obtain

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    kasife Is the comparison always from table a to table b, example you showed, id's are in table a and missing in table b, what if there is Id in table b and missing in table a? So is this comparison both way or one way?

    • kasife's avatar
      kasife
      Icon for Helper V rankHelper V
      3 years ago

      parry2kI would like to see in table A the IDs that we did not find in table B. The logic is like this: My table A is a product reservation table and my table B is sales. So I want to know in my product reservation table what is not yet sold. I need to disregard what is on the sales table and only keep what is reserved

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    kasife add a new measure as below, you can use this as visual level filter where Count is not blank

     

    Count = COUNTROWS ( EXCEPT ( VALUES ( TableA[ID] ), VALUES ( TableB[ID] ) ) )

     

    • kasife's avatar
      kasife
      Icon for Helper V rankHelper V
      3 years ago

      parry2k II think there was some detail missing that maybe I forgot because the value wasn't matching, so I used the following measurement and it worked. 

      COUNTROWS(FILTER(_ccvs, NOT tableA[id] IN VALUES(tableA[id])))

      Thanks so much

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    kasife cool, if a problem is solved with the data you know, all good. 👍