Matching between two unrelated tables

[admin_xlsior]

Hello guys,

Need a few help with my case on finding match and unmatch between two unrelated table. I know I need to use LOOKUPVALUE for this, but somehow I can't get it right.

So here is the sample data looks like :

I have a master data for Places like this ->

Place	ForHobby
Place1	Gof
Place2	Fishing
Place3	Run
Place4	Basketball
Place5	Swimming

And another table which is Person list with ther hobby and places that he already rented ->

Person	Hobby	Rent
A	Fishing
A	Golf	Place1
B	Run	Place3
B	Fishing	Place2
B	Basketball	Place4
C	Swimming	Place5
D	Golf
D	Run
E	Run
E	Basketball

As we can see in this table, with a person's hobby information, there are some with no infomation of rented place. What I expect is a list of Prospect person that I can introduced fo rent (new rent) based on their hobby, match with the hobby information of the places.

Probably something list this :

Prospect Places

Place	Person
Place1	D
Place2	A
Place3	D
Place3	E
Place4	E

Like wise, if the list turn to be person's POV and in descriptive manner : Person A there is Place1 he can rent, and for Person D, Place 1 and Place 3 applicable for rent.

In extend to this atually we can have a list like Places and how many people rented the same place.

How the DAX will be like ?

Thanks in advance.

[tamerj1]

HI @admin_xlsior 
Sory I did not understand the 2nd part of the question.

For the first part you can use https://www.dropbox.com/t/oLMQSLa6CxHS7MNF

Recommended People = 
VAR T1 = 
    FILTER (
        'Person List', 
        'Person List'[Hobby] = SELECTEDVALUE ( 'Master Data'[ForHobby] )
            && 'Person List'[Rent] = BLANK ( ) 
    )
RETURN
    CONCATENATEX (
        T1,
        [Person],
        UNICHAR ( 10 )
    )

[admin_xlsior]

Hi @tamerj1 

Let me digest and learn your pbix first, but it looks cool already 😀

Yeah, for the People's POV, means the table, column is People first. So if follows your table it may looks like : 

And what I mean by Places and how many people rented is like this :

but this is very very simple, is just a simple Count. Just saying possiblity of reports.

Thank you very much.

[tamerj1 · Accepted solution]

@admin_xlsior 
You mean like this? https://www.dropbox.com/t/RVZVoMC7wz3HEDPY

Recommended People = 
VAR T1 = 
    FILTER (
        'Person List', 
        'Person List'[Hobby] = SELECTEDVALUE ( 'Master Data'[ForHobby] )
            && 'Person List'[Rent] = BLANK ( ) 
    )
RETURN
    CONCATENATEX (
        T1,
        [Person],
        UNICHAR ( 10 )
    )

Recommended Places = 
VAR T1 = 
    SELECTCOLUMNS (
        FILTER (
            'Person List', 
            'Person List'[Rent] = BLANK ( ) 
        ),
        "@Hobby", 'Person List'[Hobby] 
    )
RETURN
    CONCATENATEX (
        FILTER ( 'Master Data', 'Master Data'[ForHobby] IN T1 ),
        'Master Data'[Place],
        UNICHAR ( 10 )
    )

Number of People Rented = COUNTROWS ( 'Person List' )