Forum Discussion
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 | IPC | Client | Desired Result |
1 | 123 | No | A | Mixed |
1 | 456 | No | B | Mixed |
1 | 789 | No | B | Mixed |
2 | 987 | Yes | A | IPC Mixed |
2 | 654 | Yes | A | IPC Mixed |
2 | 321 | No | B | IPC Mixed |
3 | 903 | No | A | A |
3 | 284 | No | A | A |
3 | 842 | No | A | A |
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]))
2 Replies
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()
)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]))
