Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous , 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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

v-deddai1-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

@Anonymous 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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

@Anonymous @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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

@Anonymous , 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.