Forum Discussion
Merging Queries vs Lookup
Hello,
I have a question about merging tables vs. using the lookup function in dax. I am currently working on a report with three tables and we have a somewhat circular relationship between them which is why I don't want to define a relationship between them. So I tried using the merge function to just get the columns I need from one table to the other, but as soon as I start adding the merge operations, the query starts taking longer to load and to complete. I am able to do this using the lookup function in dax, but the issue is, dax requires one unique value of the key/value being using for lookups in the table, otherwise it throws an error saying - single value expected but list returned. I then get around it by going in Power Query, duplicating my table, summarizing it using the group by function and ensuring that only one value for the key I'm using for lookup exists.
However, this could mean that I'd have multiple instances of the table in Power Query (depending on how I'm summarizing and filtering the data for that particular lookup). Is there a better way of doing this? Shouldn't merging be better than lookups? I'd really appreciate any help.
Thank you!
My question is,
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.
4 Replies
Hi newpbiuser01
Can you provide some sample data or picture, based on your description, cannot clearly give the suggestion that you want to get, maybe you can try to use filter() function.
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.
