Lookup Value Query

[utsavlexmark]

Hello All,

I have 2 tables as follows:

Table 1

Month     Year             URL                         Unique Visitors(Search Engine)

Table 2

Month     Year             URL                         Unique Visitors(All sources)

I want to pull the "Unique Visitors(From all sources)" from Table 2 to Table 1 for the avaiable URLs of it.

LOOKUPVALUE('Table 2'[Unique Visitors(From all sources)],'Table 2'[URL],'Table 1'[URL])

This is the query I used, but it is showing error "A table of multiple values was supplied where a single value was expected".

What I can think of is as a particular URL is repeted in different months of different year - I am getting this error.

How can I solve the issue?

Regards

Utsav

[Anonymous · Accepted solution]

Thank you raji_n 

Hi, @utsavlexmark 

Based on your description, I've created the following two tables:

table1:

table2:

Using the DAX expression you provided does bring up the problem you mentioned, as shown in the image below:

To do this, I've changed the expression to look like this, and I've matched the corresponding 'Table 2'[Unique Visitors (All sources)] with the month by URL:

Column 2 = LOOKUPVALUE(
		'Table 2'[Unique Visitors (All sources)],
		'Table 2'[URL],
		'Table 1'[URL],
		'Table 2'[Month],
		'Table 1'[Month],
		0
	)

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[utsavlexmark]

Hello,

This is working for me, but there is a small issue and I think you can guide me.

As per your suggestion - column 2 is working, but I had to select "Don't Summarize" option from setting. But if that happened - the total is not showing.

How to solve this?

Regards

Utsav

[raji_n]

@utsavlexmark use firstnonblank() to lookup, the error is due to availability of multiple matches for your keys