Measuring where two different dimensions in different tables have the same value

[scuderiluigi]

Hi everyone,

I'm trying to create a measure called "Local transactions" where the country of the seller is the same of the country of the buyer.

The country of seller is in the seller data table and the one of the buyer in the buyer table.

The relation is in the fact table (transaction id, seller id, buyer id, transaction amount)

Seller table is

seller id

Seller name

...

Seller country

Buyer table is

buyer id

Buyer name

...

Buyer country

	Buyer country
Seller country	A	B	C	D	E	F	G	H	I
A	28.863	168	609	1.837	1.771	421	4.783	488	2.191
B	229	12.301	541	1.422	301	93	1.355	158	527
C	370	439	54.955	1.830	813	301	3.184	429	1.430
D	1.915	1.918	3.150	150.878	4.003	1.274	16.973	2.022	6.683
E	4.126	2.048	6.658	14.104	113.594	6.681	24.710	6.781	37.699
F	676	178	490	1.685	3.318	33.933	4.526	1.063	4.185
G	10.816	3.871	10.914	29.763	25.535	8.323	218.472	11.492	49.332
H	376	193	701	1.543	1.372	611	3.395	38.509	2.639
I	10.673	6.351	11.749	36.400	40.991	17.265	77.473	20.677	198.749

If I use the "calculate" function I can't set the condition as "seller-country=buyer country" and the same if I use the "IF" operator.

The number of countries is high, this makes the list of combinations in and and or very long and inefficient

Than you in advance for your support

[scuderiluigi]

Hi, unfortunately I had no answer for this question

I managed to solve by doading in the fact table, in the ETL phase, the seller region and the buyer region with the join, and by creating a colum called local set to yes if the seller region is equal to the buyer region and to no  if not

I just wander if there is a different way with some dax command

[Anonymous]

Hello scuderiluigi,

I think there is some info missing. How are the tables related? What is the purpose of the measure?

[scuderiluigi]

Hi, thank you for replying.

The seller table and the buyer table have a connection to the fact table, that contains both the seller id and the buyer id. 

The purpose is to highlight the percentage of transactions that sellers make with buyers of their own country. 

[scuderiluigi]

sample pbix 

In case you need to understand better I created a sample pbix with random data