Lookup Value Query

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

raji_n
Resolver II
2 years ago
utsavlexmark use firstnonblank() to lookup, the error is due to availability of multiple matches for your keys
Anonymous
Not applicable
2 years ago
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.

Lookup Value Query.pbix33 KB
- utsavlexmark
  Helper III
  1 year ago
  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

Forum Discussion