Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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
vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

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.
MarcelBeug
Community Champion
Community Champion

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)
vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.