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

How to group data from multiple columns from single table?

I have a table of this kind 

Defect IDCreatedCreatorAssigneeResolved
101/05/2017ABC
201/06/2017BB 
301/07/2017ACC
401/08/2017ACC
501/09/2016FGH
601/10/2016ABC
701/11/2016AAF
801/12/2017AFG
901/13/2017AF

G

 

 

I would like to  combine all the Users data and see the information in this way

 

Created in monthNo.of unique users
May-20172
Jun-20163
May-20164

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: How to group data from multiple columns from single table?

i would unpivot the three colums (creator asignee and resolved in power query)

and then do a distinctcount in a measure on them sliced by the date.

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: How to group data from multiple columns from single table?

i would unpivot the three colums (creator asignee and resolved in power query)

and then do a distinctcount in a measure on them sliced by the date.

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

MarcelBeug Community Champion
Community Champion

Re: How to group data from multiple columns from single table?

That is some magic transformation.

 

Apparently all dates are in January (because of 01/13/2017 in the last row) and it results in data in May and June.

Also in your table there are no "users".

Of course we can assume that Creators, Assignees and Resolved are users, but one of the first things I was taught in IT is to assume nothing.

 

Can you explain the logic how your source data is transformed into the result?

Specializing in Power Query Formula Language (M)
Community Support
Community Support

Re: How to group data from multiple columns from single table?

Hi @belurrashmi,

 

Assume you want to count distinct Creator columns based on Year and Month values, you can use Group By in Query Editor. Also you can use DAX to create a calculated table like below:

 

Table = SUMMARIZE('Table1','Table1'[Year],'Table1'[Month],"No.unicusers",DISTINCTCOUNT('Table1'[Creator]))

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors