Forum Discussion

glennm's avatar
glennm
Regular Visitor
8 years ago
Solved

Lookupvalue with multiple values

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)

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

 

- 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:

LocationTotal requests
Spain2
Italy2
UK1
Germany2
France3
Belgium1
Sweden1
Finland1

 

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's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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