Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago

Measure to match values from multiple tables

Dear All - I need your help in creating a measure as below:-

 

If Product Code = 10 or 13 then Match Pair (City Code) with FILTER Cities “City Type” = 1 and Cities (City Code)

Return

If Match then True Else False

 

Data

Table Product Code

 

ProductCodeInvoiceIDInvoiceDetailID
69015692401552
69028142405797
69029142406096
109031702407321
139046312412467
69066742419355
69080472424346
69080472424347
69080472424348
69080532424380
69080532424381
109087012426621
139087092426636

 

Table Pair

CityCodeInvoiceDetailID
12352489632
BAR2449973
BBB2426621
BBB2426636
BBB2427038
BBB2435759
BCM2484228
BRU2484666
CDF2458090
CDG2502464
CWL2466153
CWL2481045
CWL2484425
CWL2484426
DOL2506342
EDB2406096
EDB2412467
FSTH2424346
FSTH2424347
FSTH2424348
FSTH2424380

Table Cities

 

CityCodeCityType
AAA0
AAA1
AAL0
AAN0
AAP0
AAQ0
ABE0
ABE1
ABH0
ABJ0
ABQ0
ABQ1
ABT0
ABV0
ABX0
Need Help
Show and Tell

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi gauravnarchal 

    Your desrired result isn't clear.  Please supply some tables showing the result(s) you are looking for.

    Also, what relationships have you got between these tables?

    Regards

    Phil

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi username,

     

    Please create a measure.

     

    one =

var ss = CALCULATETABLE(VALUES('Table Product Code'[InvoiceDetailID]),FILTER('Table Product Code','Table Product Code'[ProductCode] in {10,13}))

var aa = CALCULATETABLE(VALUES('Table Pair'[CityCode]),FILTER(ALL('Table Pair'),'Table Pair'[InvoiceDetailID] in ss))

return

IF(SELECTEDVALUE('Table Cities'[CityCode]) in aa,True,False)

    Best Regards

    Community Support Team _ polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      4 years ago

      Hi Anonymous - Thanks for your help, but my request is slightly different. I want to filter the cities after matching the product code with the city code.

       

      If Product Code = 10 or 13 then Match Pair (City Code) with FILTER Cities “City Type” = 1 and Cities (City Code)

      Return

      If Match then True Else False

       

      I need the results in the below table.

       

      Thanks