Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
So I have a spreadsheet with a person, project, date, and hours (amoung various other things). I have found the number of projects a person has worked on in a single day through a DAX SUMMARIZETABLE. Now I am trying to find which multiple projects a person has worked in on a single day. This would expand my table with multiple new rows to associate but I'm not sure how to do it. Would this be Power Query or DAX? What would you suggest?
This is a small part of what I am doing. I am trying to combine filters to have it show only the project data, the other projects worked in the same day for the people of the project, and a few other filters I've already created. Not quite sure where to start here...
Solved! Go to Solution.
hi, @Anonymous
For your expected output, It's easy to implement in power query.
Just try these steps as below:
Step1:
Merge the basic query with itself as below:
Step2:
Expand the Project name and rename this column.
Result:
here is pbix file, please try it.
By the way, If you want to keep the basic query, you could duplicate the basic query and do merge in the duplicate query.
Best Regards,
Lin
hi, @Anonymous
I think you 'd better use DAX to create the measure for it.
and I think you don't need to use SUMMARIZETABLE to create a new table for your expected out.
If you need to further help, please share some sample data and your expected output.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
Employee | Date | Project Number | Project Name | Hours | |
Carrie | 9/15/2019 | 1 | Star | 8 | |
Carlia | 9/15/2019 | 1 | Star | 8 | |
Carlia | 9/15/2019 | 2 | Circle | 4 | |
Ralph | 9/15/2019 | 2 | Circle | 11.5 | |
Alloria | 9/15/2019 | 2 | Circle | 8.25 | |
Alloria | 9/15/2019 | 3 | Square | 8 | |
Fondz | 9/15/2019 | 2 | Circle | 8.75 | |
Fondz | 9/15/2019 | 1 | Star | 2 | |
Fondz | 9/15/2019 | 3 | Square | 2 | |
Expected Output | |||||
Employee | Date | Project Number | Project Name | Hours | Other Project Worked |
Carrie | 9/15/2019 | 1 | Star | 8 | Star |
Carlia | 9/15/2019 | 1 | Star | 8 | Circle |
Carlia | 9/15/2019 | 1 | Star | 8 | Star |
Carlia | 9/15/2019 | 2 | Circle | 4 | Circle |
Carlia | 9/15/2019 | 2 | Circle | 4 | Star |
Ralph | 9/15/2019 | 2 | Circle | 11.5 | Circle |
Alloria | 9/15/2019 | 2 | Circle | 8.25 | Circle |
Alloria | 9/15/2019 | 3 | Square | 8 | Square |
Alloria | 9/15/2019 | 2 | Circle | 8.25 | Square |
Alloria | 9/15/2019 | 3 | Square | 8 | Circle |
Fondz | 9/15/2019 | 2 | Circle | 8.75 | Circle |
Fondz | 9/15/2019 | 1 | Star | 2 | Circle |
Fondz | 9/15/2019 | 3 | Square | 2 | Circle |
Fondz | 9/15/2019 | 2 | Circle | 8.75 | Star |
Fondz | 9/15/2019 | 1 | Star | 2 | Star |
Fondz | 9/15/2019 | 3 | Square | 2 | Star |
Fondz | 9/15/2019 | 2 | Circle | 8.75 | Square |
Fondz | 9/15/2019 | 1 | Star | 2 | Square |
Fondz | 9/15/2019 | 3 | Square | 2 | Square |
The first table expands into the second table. Not sure if Dax can do that or not..
hi, @Anonymous
For your expected output, It's easy to implement in power query.
Just try these steps as below:
Step1:
Merge the basic query with itself as below:
Step2:
Expand the Project name and rename this column.
Result:
here is pbix file, please try it.
By the way, If you want to keep the basic query, you could duplicate the basic query and do merge in the duplicate query.
Best Regards,
Lin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
120 | |
74 | |
72 | |
58 | |
49 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |