Forum Discussion
Comparing values between different tables
Hello, I have a table "LIST" composed by three columns: ID, COD, VALUE.
I have another table "CONTAINER" composed by two columns : COD , SUM_OF_VALUE
I would like to save in the SUM_OF_VALUE column the sum of the values contained in VALUE column of table LIST where the rows of table LIST and CONTAINER have the same COD.
Thanks a lot!
Ok. If you're using Power BI Desktop you just need to apply both your tables to the model, relate LIST to CONTAINER on CONTAINER[COD] 1 = * LIST[COD] (assuming your CONTAINER table contains a UNIQUE list of customers), then drag CONTAINER[COD] and LIST[VALUE] columns into a visual. PBI will automatically aggregate the result giving you what it sounds like you're after.
If this is to be done in Excel, then probably easiest to just do a merge in PQ instead of a relationship as described above in the model.
Pete
Use merge query in Power Query, or load the data into the model and use DAX to calculate (aggregate and then determine whether it is greater than 0).
11 Replies
Hi 3mi81 ,
This looks like an XY Problem.
The real question is: Why do you want to create a summary table in Power Query for data you already hold (in the LIST table)? DAX is designed to handle aggregations like this in the data model, so what's the use-case of duplicating this data?
Pete
Hi BA_Pete ,
you're probably right :).
I have tried to simplify the problem with an example that might be the solution to my problem, but it does not affectively explain the problem itself.
I have a table (LIST) that contains a list of orders (ID), with customer (COD) associated and the VALUE column contains values 1 (if the customer has certain information) , 0 otherwise
There may be multiple orders with the same customer, and some may have the VALUE property at 1 and others at 0
The second table (CONTAINER) contains the list of customers (COD)
I need to know which customers in the CONTAINER table appear at least once in the orders in the LIST table and have the property VALUE = 1
I hope I have been clearer.
Ok. If you're using Power BI Desktop you just need to apply both your tables to the model, relate LIST to CONTAINER on CONTAINER[COD] 1 = * LIST[COD] (assuming your CONTAINER table contains a UNIQUE list of customers), then drag CONTAINER[COD] and LIST[VALUE] columns into a visual. PBI will automatically aggregate the result giving you what it sounds like you're after.
If this is to be done in Excel, then probably easiest to just do a merge in PQ instead of a relationship as described above in the model.
Pete
BA_Pete Why use PQ in Excel for this? Does PowerPivot not support the functions you use
Use merge query in Power Query, or load the data into the model and use DAX to calculate (aggregate and then determine whether it is greater than 0).
Could you provide sample data?
Hi 3mi81 ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to personally thank ZhangKun and BA_Pete , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
Based on the reposted query, it is evident that we can achieve this by creating a calculated column in the container table using DAX. I have used it as sample data on my end and successfully implemented it. Therefore, please refer to the attached pbix file.
I am also including a similar thread that has already been resolved, so please review it for your understanding:
https://community.fabric.microsoft.com/t5/Desktop/Compare-value-in-two-tables/m-p/829356
I hope this should resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi 3mi81 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hello 3mi81 ,
I wanted to follow up on our previous suggestions regarding Comparing values between different tables. We would love to hear back from you to ensure we can assist you further.
If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.
