Forum Discussion
Comparing values between different tables
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).
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.
Super User
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
- PwerQueryKees
BA_Pete Why use PQ in Excel for this? Does PowerPivot not support the functions you use
- BA_Pete
It does but, in my experience, Excel users rarely want to get into PowerPivot modeling. If they did, they'd probably be using PBI already.
Pete
