Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
The table is in a direct query mode and the logic i need is
If Both Supplier and User location are in the same continent for the entire business = 1,
if Supplier and Users are in different continents ( 1 user continent) =2
if Supplier and Users are in different continents ( >1 user continent) =3
Solved! Go to Solution.
Please try
=
IF (
HASONEVALUE ( 'Table'[user ID] ),
MAXX (
'Table',
VAR UserContinent = 'Table'[user Continent]
VAR SupplierContinent = 'Table'[supplier Continent]
VAR NumOfUserContinents =
CALCULATE (
DISTINCTCOUNT ( 'Table'[user Continent] ),
ALLEXCEPT ( 'Table', 'Table'[user ID] )
)
RETURN
SWITCH (
TRUE (),
UserContinent = SupplierContinent, 1,
NumOfUserContinents = 1, 2,
3
)
)
)
Please try
=
IF (
HASONEVALUE ( 'Table'[user ID] ),
MAXX (
'Table',
VAR UserContinent = 'Table'[user Continent]
VAR SupplierContinent = 'Table'[supplier Continent]
VAR NumOfUserContinents =
CALCULATE (
DISTINCTCOUNT ( 'Table'[user Continent] ),
ALLEXCEPT ( 'Table', 'Table'[user ID] )
)
RETURN
SWITCH (
TRUE (),
UserContinent = SupplierContinent, 1,
NumOfUserContinents = 1, 2,
3
)
)
)
Worked like a charm! Thanks very much
These are in 2 different tables
If Supplier and Users are in different continents and the user is in just 1 continent then it should be 2
if Supplier and Users are in different continents and the user is in more than 1 continent, then it should be 3
Can you provide with a sample of your data table
Hello you could use switch bit i didnt understand well the 2 and 3
SWITCH(
TRUE(),
'Table'[supplier location] = 'Table'[user location],1,
AND('Table'[supplier location] <> 'Table'[user location],'Table'[users] =1),2,
AND('Table'[supplier location] <> 'Table'[user location],'Table'[users] > 1),3
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |