Lookupvalue with multiple values

[glennm]

Hi all,

I have question regarding the lookupvalue function. Currently I have 2 tables:

- 1 table with all the IDs and their corresponding location (but 1 ID can be mapped to multiple locations)

ID	Location
1	Spain
2	Italy
2	Spain
2	UK
3	Germany
4	France
5	France
6	Belgium
7	Sweden
7	Finland
8	France
9	Germany
10	Italy

- 1 table with the request for each ID.

ID
1
2
3
4
5
6
7
8
9
10

What I want to achieve is an overview like this:

Location	Total requests
Spain	2
Italy	2
UK	1
Germany	2
France	3
Belgium	1
Sweden	1
Finland	1

So this table gives an overview of the number of requests for each location, so it doesn't really matter if the ID has multiple locations, I want to count them all.

I've already tried some alternatives to the lookupvalue to receive the firstnonblank and lastnonblank so that I have multiple values but I have no idea on how to create the table above...

Does anyone have some suggestions or ideas on how I can create this kind of table?

Thanks a lot in advance!!

[v-yulgu-msft · Accepted solution]

Hi @glennm,

You could create a summarize table as below:

Result Table =
SUMMARIZE (
    'ID-Location',
    'ID-Location'[Location],
    "Total Request", COUNT ( 'ID-Location'[ID] )
)

Best regards,

Yuliana Gu