Forum Discussion

admin_xlsior's avatar
admin_xlsior
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Matching between two unrelated tables

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

PlaceForHobby
Place1Gof
Place2Fishing
Place3Run
Place4Basketball
Place5Swimming

 

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

PersonHobbyRent
AFishing 
AGolfPlace1
BRunPlace3
BFishingPlace2
BBasketballPlace4
CSwimmingPlace5
DGolf 
DRun 
ERun 
EBasketball 

 

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

PlacePerson
Place1D
Place2A
Place3D
Place3E
Place4E

 

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.

 

 

DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    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' )

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      admin_xlsior
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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' )