Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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
Thanks both @amitchandak @parry2k , have created a join table and it works now. Thanks
Hi @Anonymous ,
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
@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.
@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.
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 )
@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