cancel
Showing results for
Did you mean:
Helper I

## Obtain value from table 2 if it exist, else obtain value from table 1

Hi I have the following 2 tables. I wish to have a visualisation that sums the values of my desired output given the ID.

If there are values for the respective IDs in my priority table, I would like to use them ahead of the ones in my primary table, else take the values from the primary table.

I am doing this because I hope to do it via DAX and not merge everything on a single table on Power Query.

Primary table:

 ID Value 1 5 2 7 3 8 4 10 5 3 6 1

Priority table:

 ID Overwrite Value 1 7 2 9 5 4 6 2

Desired output

 ID Value 1 7 2 9 3 8 4 10 5 4 6 2

Could I get some help from experts please  @parry2k @amitchandak

1 ACCEPTED SOLUTION
Super User

@zyc , Need to create a common table

ID  = distinct(union(distinct(Table1[ID], distinct(Table2[ID])))

Join on ID

Try a measure like

max(Table1[Value], Table2[Value])

of

if(Max(Table1[Value]) > Max(Table2[Value]) ,Max(Table1[Value]) ,Max(Table2[Value]) )

Plot this with ID of ID table

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
6 REPLIES 6
Helper I

Thanks both @amitchandak @parry2k , have created a join table and it works now. Thanks

Community Support

Hi @zyc ,

You can create a calculated column:

``value2 = var a = SUMX(RELATEDTABLE(Priority),Priority[Overwrite Value]) return IF(a = BLANK(),Primary[Value], a)``

Or you can create a caluculated table:

``Table = SUMMARIZE(Primary,Primary[ID],"value",IF(SUM(Priority[Overwrite Value])=BLANK(),SUM(Primary[Value]),SUM(Priority[Overwrite Value])))``

You can refer to the pbix file.

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

Best Regards,

Dedmon Dai

Super User

@zyc doesn't matter if you have a same number of columns in both tables or not, you should still able to create a table with distinct IDs, not sure why you cannot do it, and if you are getting any errors. Be more specific about what you tried and what kind of error you are getting.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@zyc @amitchandak  solution will work assuming that Overwrite table value is always greater than the Primary table value, in case Overwrite table value is less than the Primary table value, it will take the Primary table value instead of Overwrite table value.

If your ask is to always take value from Overwrite table regardless it is more or less then you need to change the DAX measure as below.

``````Id Value =
VAR __overWriteValue = MAX ( Overwrite[Overwrite Value] )
RETURN
IF ( __overWriteValue == BLANK(), MAX ( Primary[Value] ), __overWriteValue )``````

Rest you need to follow to create a common table for Ids as @amitchandak  recommended using UNION.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

Hi @parry2k @amitchandak thank you both for your inputs. I have tried doing the below and it works. However, I am unable to do a union because in my actual data, these 2 tables do not actually have the same number of columns. The ones in the example shown above are only shown because they are relevant. Hence, I think because of that, the summation of the measure does not come out well (even tho there are no errors)

Is there a way to incorporate a filter for when Primary[ID] = Overwrite[ID]?

```Id Value =
VAR __overWriteValue = MAX ( Overwrite[Overwrite Value] )
RETURN
IF ( __overWriteValue == BLANK(), MAX ( Primary[Value] ), __overWriteValue )```

Super User

@zyc , Need to create a common table

ID  = distinct(union(distinct(Table1[ID], distinct(Table2[ID])))

Join on ID

Try a measure like

max(Table1[Value], Table2[Value])

of

if(Max(Table1[Value]) > Max(Table2[Value]) ,Max(Table1[Value]) ,Max(Table2[Value]) )

Plot this with ID of ID table

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.