Reply
MightyRabbit
Frequent Visitor
Partially syndicated - Outbound

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,

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

Syndicated - Outbound

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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

2 REPLIES 2
MightyRabbit
Frequent Visitor

Syndicated - Outbound

Hi @rubayatyasmin ;

That works perfectly. Thanks 🙂

rubayatyasmin
Super User
Super User

Syndicated - Outbound

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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)