Add target value to other table

Hi guys, I have following source table: ID USER_ID # ORD 1 U1 5 2 U1 10 3 U1 5 4 U2 50 5 U3 1 Then I also have additional table with target values: USER_...

Need Help

Tips and Tricks

v-yuta-msft
7 years ago
Anonymous ,

So if the USER_ID does not exist in Source table, it can be also understanded as the max(lastest) USER_ID in additional table, right? If so, you can create a new USER_ID column in Source table using DAX below:

New USER_ID = IF('Source Table'[USER_ID] = RELATED('Additional Table'[USER_ID]), RELATED('Additional Table'[USER_ID]), MAX('Additional Table'[USER_ID]))

Then you can create a measure based on the new USER_ID column to achieve the total result.

SUM #ORD = CALCULATE(SUM('Source Table'[# ORD]), ALLEXCEPT('Source Table', 'Source Table'[New USER_ID]))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Add target value to other table.pbix38 KB

Anonymous

Not applicable

7 years ago

Yes, you're right. That's the easiest solution. But what can happen is that I have in target table USER1, USER2, but in source table records for USER1, USER2, USER3, USER4. Then if I use USER_ID from source table, relation doesn't work correctly and if I take it from target, it works, but shows only USER1, USER2 and rest is shown as blank.