The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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], "," ) )
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
102 | |
82 | |
62 | |
56 |
User | Count |
---|---|
254 | |
119 | |
115 | |
99 | |
71 |