The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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
Hello scuderiluigi,
I think there is some info missing. How are the tables related? What is the purpose of the measure?
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.
In case you need to understand better I created a sample pbix with random data
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |