topic Re: Create a Flag calculated Column for MyCompany clients in DAX Commands and Tips

Create a Flag calculated Column for MyCompany clients

AvalBuroAdmin — Tue, 08 Sep 2020 22:03:59 GMT

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.

Re: Create a Flag calculated Column for MyCompany clients

Greg_Deckler — Tue, 08 Sep 2020 22:20:28 GMT

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

Re: Create a Flag calculated Column for MyCompany clients

AvalBuroAdmin — Tue, 08 Sep 2020 22:47:25 GMT

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?

Re: Create a Flag calculated Column for MyCompany clients

Greg_Deckler — Tue, 08 Sep 2020 23:45:26 GMT

AvalBuroAdmin That's why I used SUMMARIZE.

Re: Create a Flag calculated Column for MyCompany clients

amitchandak — Wed, 09 Sep 2020 02:26:38 GMT

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