cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AvalBuroAdmin
Regular Visitor

Create a Flag calculated Column for MyCompany clients

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
ClientIdProviderFlag
JhonMyCompanySharedClient
JhonProvider02SharedClient
JhonProvider17SharedClient
LuisProvider11NotMyClient
LuisProvider03NotMyClient
CarlosMyCompanyExclusiveClient
PedroProvider16SharedClient
PedroMyCompanySharedClient

 

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.

1 ACCEPTED SOLUTION

@AvalBuroAdmin That's why I used SUMMARIZE.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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?

@AvalBuroAdmin That's why I used SUMMARIZE.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors