Forum Discussion
Merging Queries vs Lookup
Hi newpbiuser01
You can use dax to solve it.
You can refer to the following examples.
Table A:
Vendor Active = IF(COUNTROWS(FILTER('Table C',[Vendor]=EARLIER('Table A'[Vendor])&&[Status]="Active"))>0,"Yes","No") Location = var a=FILTER('Table B',[Vendor]=EARLIER('Table A'[Vendor])) return CONCATENATEX(a,[Location],",")Table B:
In Scope = IF(COUNTROWS(FILTER('Table C',[Vendor]=EARLIER('Table B'[Name])&&[Status]="Active"))>0,"Yes","No") Vendor Manager = var a=SUMMARIZE(FILTER('Table C',[Vendor]=EARLIER('Table B'[Name])),[Manager]) return CONCATENATEX(a,[Manager],",") Active Programs = var a=FILTER('Table A','Table A'[Vendor]=EARLIER('Table B'[Name])&&[Status]="Running") return MAXX(FILTER(a,[Date]=MAXX(a,[Date])),[Program])Table C
Ongoing Programs = CONCATENATEX(FILTER('Table A',[Vendor]=EARLIER('Table C'[Vendor])&&[Status]="Running"),[Program],",") Location = var a=FILTER('Table B',[Vendor]=EARLIER('Table C'[Vendor])) return CONCATENATEX(a,[Location],",") Vendor Number = MAXX(FILTER('Table B',[Name]=EARLIER('Table C'[Vendor])),[Number])Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
So as an example, I have three tables (I've kept the names and the values generic for confidentiality reasons). All three tables are used throughout the report (or in different tabs within the report) by themselves but they need columns/data from each other. As an example, I need to include a couple of columns from Table B and C in Table A for a report, and similarly for Table B related reports, I need columns from Table A and C etc.
| Table A | |||||||
| Program | Type | Person | Date | Vendor | Status | Vendor Active? | Location |
| Program1 | 1 | abc | 2012-10-13 | A | Running | If we have any records in table C for that vendor where the Status = Active, then Yes, else No | Lookup a concatenated list of locations for that vendor from Table B |
| Program2 | 2 | def | 2022-01-08 | A | Running | ||
| Program3 | 3 | ghi | 2023-03-10 | A | Completed | ||
| Program4 | 4 | abd | 2022-04-11 | A | Running | ||
| Program1 | 5 | dfd | 2021-05-08 | A | Completed | ||
| Program5 | 6 | dfd | 2012-10-13 | A | Completed | ||
| Program1 | 7 | kij | 2022-01-08 | B | Completed | ||
| Program2 | 8 | jih | 2023-03-10 | B | Running | ||
| Program1 | 9 | abc | 2022-04-11 | B | Completed | ||
| Program3 | 10 | def | 2021-05-08 | B | Completed | ||
| Program4 | 11 | ghi | 2021-05-08 | B | Running | ||
| Table B | ||||||
| Name | Number | Location | Vendor | In Scope? | Vendor Manager | Active Programs |
| A | 1 | US | A | If we have any records in table C for that vendor where the Status = Active, then Yes, else No | Lookup concatenated list of managers from Table C | Lookup to see the latest program that's in progress for this vendor from Table A |
| B | 2 | Canada | A | |||
| C | 3 | Australia | B | |||
| D | 4 | UK | B | |||
| E | 5 | Germany | C | |||
| F | 6 | France | D | |||
| G | 7 | China | E | |||
| H | 8 | India | F |
| Table C | ||||||||
| Vendor | ID | Manager | Vendor Manager | Status | Date | Ongoing Programs | Location | Vendor Number |
| A | 1 | xyz | aac | Active | 2012-10-13 | Lookup to see if there's any ongoing programs from Table A | Lookup a concatenated list of locations for that vendor from Table B | Lookup from Table B |
| A | 2 | xyx | abc | Inactive | 2022-01-08 | |||
| B | 3 | abc | def | Active | 2023-03-10 | |||
| B | 4 | def | aac | Active | 2022-04-11 | |||
| B | 5 | ghi | abc | Active | 2021-05-08 | |||
| C | 6 | xyz | aac | Active | 2012-10-13 | |||
| D | 7 | xyx | abc | Inactive | 2022-01-08 | |||
| E | 8 | abc | def | Active | 2023-03-10 |
Now the issue here is, if I do decide to merge the data tables in Power Query, the report becomes very very very slow while loading, because I am merging columns from 1 to the other 2, and then repeating that three times.
I tried doing lookupvalue in DAX, but as you can see, each table can have multiple records under the same vendor name, so if I do lookup status based on the vendor name, lookupvalue function returns an error saying, this function needs a single value to be returned, a list was returned.
If I create a relationship between the three, I end up getting circular dependency between the tables, so that doesn't work.
The only alternative is, to have three tables - A, B, and C in Power Query and then duplicating these, so Table A - summarized, Table B - summarized, and Table C - summarized where I summarize the data based on the vendor name, and then use these to lookup the data in Table A, Table B and C as calculated columns in DAX. This works, but if we have a large amount of data, we'd now have double the tables in the report.
Is there another way to do this?
I hope this makes sense!
Hi newpbiuser01
You can use dax to solve it.
You can refer to the following examples.
Table A:
Vendor Active = IF(COUNTROWS(FILTER('Table C',[Vendor]=EARLIER('Table A'[Vendor])&&[Status]="Active"))>0,"Yes","No")
Location = var a=FILTER('Table B',[Vendor]=EARLIER('Table A'[Vendor]))
return CONCATENATEX(a,[Location],",")
Table B:
In Scope = IF(COUNTROWS(FILTER('Table C',[Vendor]=EARLIER('Table B'[Name])&&[Status]="Active"))>0,"Yes","No")
Vendor Manager = var a=SUMMARIZE(FILTER('Table C',[Vendor]=EARLIER('Table B'[Name])),[Manager])
return CONCATENATEX(a,[Manager],",")
Active Programs = var a=FILTER('Table A','Table A'[Vendor]=EARLIER('Table B'[Name])&&[Status]="Running")
return MAXX(FILTER(a,[Date]=MAXX(a,[Date])),[Program])
Table C
Ongoing Programs = CONCATENATEX(FILTER('Table A',[Vendor]=EARLIER('Table C'[Vendor])&&[Status]="Running"),[Program],",")
Location = var a=FILTER('Table B',[Vendor]=EARLIER('Table C'[Vendor]))
return CONCATENATEX(a,[Location],",")
Vendor Number = MAXX(FILTER('Table B',[Name]=EARLIER('Table C'[Vendor])),[Number])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thank you, this works. I think you're right, the best way to do it is in DAX. It doesn't hurt the performance of the report like the merging does.