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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
graefs
Frequent Visitor

Custom Column for Unique ID

I am looking to create a column that categorizes each group based on the result of multiple columns. 

 

A: IPC = No; Client = A

B: IPC = No;  Client = B

Mixed: IPC = No; Client = A and B

IPC: IPC = Yes; Client = A

IPC Mixed: IPC = Yes; Client = B

 

It seems simple but has left me stumped. Any help is greatly appreciated.

 

 

Data Example:

Group

ID

IPCClientDesired Result

1

123

NoAMixed

1

456

NoBMixed

1

789

NoBMixed

2

987

YesAIPC Mixed

2

654

YesAIPC Mixed

2

321

NoB

IPC Mixed

3

903

NoA

A

3

284

NoA

A

3

842

NoA

A

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@graefs 

you can try this

 

Column =
var _ipc=maxx(FILTER('Table','Table'[Group]=EARLIER('Table'[Group])&&'Table'[IPC]="Yes"),'Table'[IPC])
var _count =CALCULATE(DISTINCTCOUNT('Table'[Client]),ALLEXCEPT('Table','Table'[Group]))
return if(_ipc="Yes" && _count>1,"IPC Mixed",if(_ipc="" && _count>1,"Mixed",'Table'[Client]))
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@graefs 

you can try this

 

Column =
var _ipc=maxx(FILTER('Table','Table'[Group]=EARLIER('Table'[Group])&&'Table'[IPC]="Yes"),'Table'[IPC])
var _count =CALCULATE(DISTINCTCOUNT('Table'[Client]),ALLEXCEPT('Table','Table'[Group]))
return if(_ipc="Yes" && _count>1,"IPC Mixed",if(_ipc="" && _count>1,"Mixed",'Table'[Client]))
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Kaviraj11
Super User
Super User

Hi,

 

Create a new column:

 

Category =
SWITCH(
TRUE(),
'Table'[IPC] = "No" && 'Table'[Client] = "A", "A",
'Table'[IPC] = "No" && 'Table'[Client] = "B", "B",
'Table'[IPC] = "No" && 'Table'[Client] IN {"A", "B"}, "Mixed",
'Table'[IPC] = "Yes" && 'Table'[Client] = "A", "IPC",
'Table'[IPC] = "Yes" && 'Table'[Client] = "B", "IPC Mixed",
BLANK()
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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