Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Karthikreddy523
Frequent Visitor

How to create a measure for this?

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@Karthikreddy523 

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
)
)
)

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

@Karthikreddy523 

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

Karthikreddy523
Frequent Visitor

These are in 2 different tables

tamerj1
Super User
Super User

Hi @Karthikreddy523 

is everything is in one table?

Karthikreddy523
Frequent Visitor

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

eliasayyy
Memorable Member
Memorable Member

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

)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.