Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone
I need to create a Calculated Column to flag my company's clients.
Algorithm is simple but tricky:
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.
Solved! Go to Solution.
@AvalBuroAdmin That's why I used SUMMARIZE.
@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"
)
@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"
)
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |