Forum Discussion

MightyRabbit's avatar
MightyRabbit
Frequent Visitor
3 years ago
Solved

Query to subtract column from different Table

Dear all, 

Could you please kindly help with the querry to subtract 1 column from another table.
I have "table 1" with Column "No" Like the one below:

No
A1
A2
A1
A2
A3
A4
A5

 

I have "Table 2" with Column "No_deleted" as following:

No_deleted
A1
A4


How can I subtract Column "No" from table 1 to column "No_deleted" in table 2, and putting the result like following:

NoResult
A10
A2A2
A10
A2A2
A3A3
A40
A5A5


Thank you very much for your help.
Best regards,

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    Hi, MightyRabbit 

     

    try to use calculate and all with IF and NOT DAX func

     

    the code will be something like

     

    Result = 
    CALCULATE(
        IF(
            NOT(CONTAINS(table2, table2[No_deleted], table1[No])),
            table1[No],
            "0"
        ),
        ALL(table2)
    )

     

2 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, MightyRabbit 

     

    try to use calculate and all with IF and NOT DAX func

     

    the code will be something like

     

    Result = 
    CALCULATE(
        IF(
            NOT(CONTAINS(table2, table2[No_deleted], table1[No])),
            table1[No],
            "0"
        ),
        ALL(table2)
    )