cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zyc
Helper I
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:

IDValue
15
27
38
410
53
61

 

Priority table:

IDOverwrite Value
17
29
54
6

2

 

Desired output

IDValue
17
29
38
410
54
62

 

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

View solution in original post

6 REPLIES 6
zyc
Helper I
Helper I

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

v-deddai1-msft
Community Support
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)

Capture59.PNG

 

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

 

Capture60.PNG

 

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

parry2k
Super User
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.

 

 






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

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





parry2k
Super User
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.

 

Follow us on LinkedIn

 

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.

 






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

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





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 )

 

amitchandak
Super User
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!