Measure to match values from multiple tables

[gauravnarchal]

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

ProductCode	InvoiceID	InvoiceDetailID
6	901569	2401552
6	902814	2405797
6	902914	2406096
10	903170	2407321
13	904631	2412467
6	906674	2419355
6	908047	2424346
6	908047	2424347
6	908047	2424348
6	908053	2424380
6	908053	2424381
10	908701	2426621
13	908709	2426636 Table Pair CityCode InvoiceDetailID 1235 2489632 BAR 2449973 BBB 2426621 BBB 2426636 BBB 2427038 BBB 2435759 BCM 2484228 BRU 2484666 CDF 2458090 CDG 2502464 CWL 2466153 CWL 2481045 CWL 2484425 CWL 2484426 DOL 2506342 EDB 2406096 EDB 2412467 FSTH 2424346 FSTH 2424347 FSTH 2424348 FSTH 2424380 Table Cities CityCode CityType AAA 0 AAA 1 AAL 0 AAN 0 AAP 0 AAQ 0 ABE 0 ABE 1 ABH 0 ABJ 0 ABQ 0 ABQ 1 ABT 0 ABV 0 ABX 0

[v-rongtiep-msft]

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]

Hi @v-rongtiep-msft - 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

[PhilipTreacy]

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