Summarizing table

[Anonymous]

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.

[Mariusz]

Hi @Anonymous 

Please see the attached file with 2 solutions, one in Power query and second using DAX table

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

@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.

[Mariusz · Accepted 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], 
            "," )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[amitchandak]

First union  -< Function , Employee 1 >, < Function , Employee 2 >

and then use concatenatex

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...

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

[Anonymous]

@amitchandak i tried what you suggested but it is giving an error.

[amitchandak]

Please share the formula in the text. Difficult to check it, in the image.

[Anonymous]

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], "," ) )
)