Create a Flag calculated Column for MyCompany clients

[AvalBuroAdmin]

Hello Everyone

I need to create a Calculated Column to flag my company's clients.

Algorithm is simple but tricky:

• If a Client only has MyCompany as provider it should be marked as ExclusiveClient
• If a Client has MyCompany and any other company as providers should me marked as SharedClient
• Finally if the client doesnt have MyCompany as provider it should be marked as NotMyClient

ClientId	Provider	Flag
Jhon	MyCompany	SharedClient
Jhon	Provider02	SharedClient
Jhon	Provider17	SharedClient
Luis	Provider11	NotMyClient
Luis	Provider03	NotMyClient
Carlos	MyCompany	ExclusiveClient
Pedro	Provider16	SharedClient
Pedro	MyCompany	SharedClient

Last but not least, I cant use row count because we have the same information for all months of the year in the same table

Any suggestions about how to create the Calculated Column.

Rewards.

[amitchandak]

@AvalBuroAdmin , As a new column

new column =

var _mycomp = countx(filter(Table,[ClientId] =earlier([ClientId]) && [Provider] ="MyCompany"),[ClientId])+0
var _ocomp = countx(filter(Table,[ClientId] =earlier([ClientId]) && [Provider] <> "MyCompany"),[ClientId])+0
return
Switch() ( True() ,
_mycomp>0 && _ocomp =0 , "ExclusiveClient",
_mycomp>0 && _ocomp >0 ,"SharedClient",
"NotMyClient"
)

[Greg_Deckler]

@AvalBuroAdmin - Maybe:

Flag = 
  VAR __Table = SELECTCOLUMNS(SUMMARIZE(FILTER('Table',[ClientId]=EARLIER([ClientId])),[Provider]),"Count",COUNTROWS('Table'),"Provider",[Provider])
RETURN
  SWITCH(
    COUNTROWS(__Table)=1 && "MyCompany" IN DISTINCT(__Table),"ExclusiveClient",
    "MyCompany" IN DISTINCT(__Table),"SharedClient",
    "NotMyClient"
  )

[AvalBuroAdmin]

@Greg_Decklerthe problem is I can have more than 1 row with the same client and provider because in the same table same data is detailed for every month of the year (and others columns). Is there a way to create the flag without counting rows or calcluate this column in power query editor?

[Greg_Deckler · Accepted solution]

@AvalBuroAdmin That's why I used SUMMARIZE.