cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Juraj_C_SK Frequent Visitor
Frequent Visitor

Add target value to other table

Hi guys,

 

I have following source table:

IDUSER_ID# ORD
1U15
2U110
3U15
4U250
5U31

 

Then I also have additional table with target values:

USER_IDTarget
U125
U250

 

What I'd like to do is following:

USER_IDSUM ORDTarget
U12025
U25150

 

Could you please advise me how to add target value (for each USER_ID) to my visualisation (based on source table data).

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Add target value to other table

@Juraj_C_SK ,

 

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

2.PNG  

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

1.PNG  

 

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.

6 REPLIES 6
Super User
Super User

Re: Add target value to other table

@Juraj_C_SK  Could you please confirm - how your final expected has 51 for USER_ID U2. Is it you are adding U2 and U3 data ? If yes, what is your SUM ORD field logic....



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Juraj_C_SK Frequent Visitor
Frequent Visitor

Re: Add target value to other table

Hi, Target values are defined by me (hardcoded). Of course, in reality I have much more USERs.

 

SUM ORD is basic calculation of values from source table (column # ORD) = In souce table we have 5 records, 3 out of them for U1, so SUM of column '# ORD' is 5+10+5 = 20.

 

thanks 

az38 Regular Visitor
Regular Visitor

Re: Add target value to other table

If I understand correct you should just add relationship between tables, then add to visual fields USER_ID, Sum of #ORD and average (or max, or min, whatever) Target

Juraj_C_SK Frequent Visitor
Frequent Visitor

Re: Add target value to other table

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.

az38 Regular Visitor
Regular Visitor

Re: Add target value to other table

For your example from start post it will look like this

 

Снимок2.PNG

 

Снимок.PNG

 

you have no target for U3 in target table, so, you have a blank target in visual


Community Support Team
Community Support Team

Re: Add target value to other table

@Juraj_C_SK ,

 

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

2.PNG  

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

1.PNG  

 

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.