Merging Queries vs Lookup

[newpbiuser01]

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, 

[Anonymous]

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.

[newpbiuser01]

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! 

[Anonymous · Accepted solution]

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.

[newpbiuser01]

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.