Substract values from different tables

[Anonymous]

Hi,

i have 2 tables.  T_USRBARCODE is common variable.  in table1 a have column PN class Number and in second table i have column I_CLASS.
Now, want to subtract one value from another (PN class Number- I_CLASS), so the result would be, in case from picture:

1. row --->1
2. row --->0
3. row --->2

4. row --->2

5. row --->1  ect.

i tried measure=sum(table1[PN class Number])-sum(table 2[I_CLASS]) and also tried with CALCULATE funtion but nothing worked.

Pls help me with this.

Tnx

[v-yingjl]

Hi @Anonymous ,

You can create a bridge table include the 'code' column to change many-to many relationship into many-to-one relationship between two tables because many-to-many relationship cannot find the unique value which corresponds with each rows.

Refer this microsoft document:

Apply many-many relationships in Power BI Desktop 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Both table are created as result of merging tables that are in direct query mode. So every time i refresh i get new 'codes'. Problem is that sql server, on which i am connected with diect query, is filled by operators in production and there is always some percent of wrong entryes. And in that case i wont always have 1-1 in codes. What hapens to codes that don't have counterparts?

How goes the measure you have writen for subtracting?

[parry2k]

@Anonymous relationship between these tables is 1 to 1 or 1 to many, share screenshot of relationship diagram?

[Anonymous]

Relation is many to many