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,
I have a set of data that describes case work coming into a support organization, including who owns it during different points over the case lifecycle. What I need to do is describe each case as having been owned by a group, i.e. 'Red', 'Blue' or 'Red&Blue', based on the Team that shows up in the ownership history. I have a separate table that maps the Teams to their group, something like the following:
Team A = Red
Team B = Blue
(In reality, I have many more Team combinations, but just trying to keep it simple to get the logic)
Tables:
Case[Case]
User[Team]
History[Time]
Case Team Time
123 A 11:23
123 B 12:34
123 A 12:54
456 B 1:50
456 B 1:56
789 A 10:21
234 A 7:45
234 A 7:52
234 B 7:59
I'd like to produce a table that includes the following:
Case Group
123 Red&Blue
456 Blue
789 Red
234 Red&Blue
I intend to use the output against other attributes within the Case table, so whether it actually needs to be a table or a measure may be immaterial(?). Any help is appreciated - thank you in advance.
Solved! Go to Solution.
Hi @Anonymous
This calculated table might be close.
Table = VAR DistinctValues = SUMMARIZECOLUMNS('Table1'[Case],'Table1'[Team]) VAR Final = SUMMARIZECOLUMNS('Table1'[Case]) RETURN ADDCOLUMNS( Final , "Group" , CONCATENATEX( FILTER(DistinctValues,[Case] = EARLIER('Table1'[Case])), [Team],","))
Hi,
You may refer to my solution here.
Hope this helps.
This one had already been answered.
Hi @Anonymous
This calculated table might be close.
Table = VAR DistinctValues = SUMMARIZECOLUMNS('Table1'[Case],'Table1'[Team]) VAR Final = SUMMARIZECOLUMNS('Table1'[Case]) RETURN ADDCOLUMNS( Final , "Group" , CONCATENATEX( FILTER(DistinctValues,[Case] = EARLIER('Table1'[Case])), [Team],","))
Thank you Phil, this has helped open me up to the use of Variables, something I hadn't been familiar with previously. This is a huge help - and I'm finding I can do much more with it now!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |