Forum Discussion
Multiple IF and SWITCH for a calculated column
I want to see the overlap between multiple groups. The users in each group are working on a site and I need to know which respective groups interracted with each site.
I have user and site columns and want to get a formula in order to calculate the "Wanted" column. As you may be able to see, I only care for specific groups that touch each site. I only care about a, b, and d. This means that I need to track all interactions between:
abd
ab
bd
ad
a
b
d
The goal is to have a pie chart to show the count of real sites broken into the groups that work on them. I hope to do this while respecting the individuality of each site.
| Site | User | Wanted |
| 1 | a | abd |
| 1 | b | abd |
| 1 | a | abd |
| 1 | d | abd |
| 2 | c | bd |
| 2 | b | bd |
| 2 | d | bd |
| 3 | a | ab |
| 3 | b | ab |
| 4 | a | ad |
| 4 | c | ad |
| 4 | d | ad |
| 4 | e | ad |
| 5 | a | a |
| 5 | c | a |
| 5 | e | a |
Here is another example that is also acceptable:
| Site | User | Wanted |
| 1 | a | abd |
| 1 | b | abd |
| 1 | c | |
| 1 | d | abd |
| 1 | a | abd |
| 2 | b | bd |
| 2 | d | bd |
| 3 | a | ab |
| 3 | b | ab |
| 4 | a | ad |
| 4 | c | |
| 4 | d | ad |
| 4 | e | |
| 5 | a | a |
| 5 | c | |
| 5 | e |
I do not think it is necessary to have the value reflected on every row of each site, however I am open to both types of solutions.
3 Replies
- Greg_Deckler
Community Champion
Seems like:
Wanted Column = CONCATENATEX( FILTER('Table',[Site] = EARLIER([Site]) && [User] IN { "a", "b", "d" }), [User],,"" )You are on the right path. However, In my example, I should have included a different dimension. I just updated the tables in the original post to reflect this.
If the "ID" 1 has multiple "a" values, I only want to have the value "a" once in the "wanted column" and the solution you posted would leave the result "aabd" rather than the desired "abd"
v-lili6-msftCommunity Support
hi Anonymous
Just create a calculate column as below:
Column = IF( [User] IN { "a", "b", "d" }, CONCATENATEX( FILTER('Table',[Site] = EARLIER([Site]) && [User] IN { "a", "b", "d" }), [User],,"" ) )Regards,
Lin
