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.
Hi All,
i currently have a requirement to create a summarized table.my data is stored in a sharepoint list.
Now i just want to do summarize a table. currently i have a table like below.
Function | Problem Statement | Employee Name 1 | Employee Name 2 | Employee Name 3 | Employee Name 4 | Employee Name 5 |
Finance | Automation | ajay | ||||
Finance | Automation | suresh | ||||
Finance | Automation | ramesh |
The problem statement can be same for either 2 or 3 or even 5 employees. 5 is maximum. So i want to create a table where in i have distinct problem statement along with the employees who have worked upon in it in a single column like below:
Function | Employee Name | Problem Statement |
Finance | ajay,suresh,ramesh | Automation |
Kindly help me to achieve this as i am stuck out on this.
Solved! Go to Solution.
Hi @Anonymous
Try this instead
Table DAX =
SUMMARIZE(
'Table',
'Table'[Function],
'Table'[Problem Statement],
"Names",
CONCATENATEX(
GENERATE(
'Table',
VAR __columns = { [Employee Name 1], 'Table'[Employee Name 2], 'Table'[Employee Name 3], 'Table'[Employee Name 4], 'Table'[Employee Name 5] }
VAR __filter = FILTER( __columns, [Value] <> BLANK() )
RETURN __filter
),
[Value],
"," )
)
@Mariusz i tried your solution, however there is one issue. it does concatenate the employee names and creating a simple table view but it also concatenates all the employees irrespective of their problem statement. so the employee column is the same for everyone.
Here is a screenshot of table and code below:
Please advise.
Hi @Anonymous
Try this instead
Table DAX =
SUMMARIZE(
'Table',
'Table'[Function],
'Table'[Problem Statement],
"Names",
CONCATENATEX(
GENERATE(
'Table',
VAR __columns = { [Employee Name 1], 'Table'[Employee Name 2], 'Table'[Employee Name 3], 'Table'[Employee Name 4], 'Table'[Employee Name 5] }
VAR __filter = FILTER( __columns, [Value] <> BLANK() )
RETURN __filter
),
[Value],
"," )
)
First union -< Function , Employee 1 >, < Function , Employee 2 >
and then use concatenatex
In below summarize can be replaced with additional columns also
union(
SUMMARIZE(filter(table,not(isblank(table[employee 1]))), table[Function],table[employee 1]),
SUMMARIZE(filter(table,not(isblank(table[employee 2]))), table[Function],table[employee 2])
SUMMARIZE(filter(table,not(isblank(table[employee 3]))), table[Function],table[employee 3])
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Please share the formula in the text. Difficult to check it, in the image.
Here you go @amitchandak @Mariusz
Table Employee Name =
VAR __select =
SELECTCOLUMNS(
'Rewards & Recognition Award Nomination',
"Function", 'Rewards & Recognition Award Nomination'[NominationFunction],
"Problem Statement",'Rewards & Recognition Award Nomination'[Problem Statement],
"Solution",'Rewards & Recognition Award Nomination'[Solution],
"Contribution",'Rewards & Recognition Award Nomination'[Contribution],
"Stakeholder Feedback",'Rewards & Recognition Award Nomination'[StakeholderFeedback],
"Parameter",'Rewards & Recognition Award Nomination'[Parameter],
"Quater",'Rewards & Recognition Award Nomination'[Quarter],
"Names",
VAR __columns = { 'Rewards & Recognition Award Nomination'[Star Award Employee 1], 'Rewards & Recognition Award Nomination'[Star Award Employee 2], 'Rewards & Recognition Award Nomination'[Star Award Employee 3], 'Rewards & Recognition Award Nomination'[Star Award Employee 4], 'Rewards & Recognition Award Nomination'[Star Award Employee 5] }
var problemstatement = {'Rewards & Recognition Award Nomination'[Problem Statement]}
VAR __filter = FILTER( __columns, problemstatement = 'Rewards & Recognition Award Nomination'[Problem Statement] )
RETURN CONCATENATEX( __filter, [Value], "," )
)
return
ADDCOLUMNS(
SUMMARIZE(
'Rewards & Recognition Award Nomination',
'Rewards & Recognition Award Nomination'[NominationFunction],
'Rewards & Recognition Award Nomination'[Problem Statement],
'Rewards & Recognition Award Nomination'[Solution],
'Rewards & Recognition Award Nomination'[Contribution],
'Rewards & Recognition Award Nomination'[StakeholderFeedback],
'Rewards & Recognition Award Nomination'[Parameter],
'Rewards & Recognition Award Nomination'[Quarter]
),
"Employee Nominated",CALCULATE( CONCATENATEX( __select, [Names], "," ) )
)
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 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |