cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayant43 Frequent Visitor
Frequent Visitor

Summarizing table

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.
 

FunctionProblem StatementEmployee Name 1Employee Name 2Employee Name 3Employee Name 4Employee Name 5
FinanceAutomationajay    
FinanceAutomation suresh   
FinanceAutomation  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:

FunctionEmployee NameProblem Statement
Financeajay,suresh,rameshAutomation

 

Kindly help me to achieve this as i am stuck out on this.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Summarizing table

Hi @jayant43 

 

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.

 

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Summarizing table

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

 





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


jayant43 Frequent Visitor
Frequent Visitor

Re: Summarizing table

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

image.png

Super User IV
Super User IV

Re: Summarizing table

Hi @jayant43 

 

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.

 

jayant43 Frequent Visitor
Frequent Visitor

Re: Summarizing table

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

image.pngimage.png

 

Please advise.

Super User IV
Super User IV

Re: Summarizing table

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





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


jayant43 Frequent Visitor
Frequent Visitor

Re: Summarizing table

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

Re: Summarizing table

Hi @jayant43 

 

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.

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors