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
Anonymous
Not applicable

DAX/Power Query Problem: Creating records for multiple projects person was in for single day

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

1 ACCEPTED 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:

3.JPG

Step2:

Expand the Project name and rename this column.

4.JPG

Result:

5.JPG

 

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

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

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

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

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

EmployeeDateProject NumberProject NameHours 
Carrie9/15/20191Star8 
Carlia9/15/20191Star8 
Carlia9/15/20192Circle4 
Ralph9/15/20192Circle11.5 
Alloria9/15/20192Circle8.25 
Alloria9/15/20193Square8 
Fondz9/15/20192Circle8.75 
Fondz9/15/20191Star2 
Fondz9/15/20193Square2 
      
Expected Output
EmployeeDateProject NumberProject NameHoursOther Project Worked
Carrie9/15/20191Star8Star
Carlia9/15/20191Star8Circle
Carlia9/15/20191Star8Star
Carlia9/15/20192Circle4Circle
Carlia9/15/20192Circle4Star
Ralph9/15/20192Circle11.5Circle
Alloria9/15/20192Circle8.25Circle
Alloria9/15/20193Square8Square
Alloria9/15/20192Circle8.25Square
Alloria9/15/20193Square8Circle
Fondz9/15/20192Circle8.75Circle
Fondz9/15/20191Star2Circle
Fondz9/15/20193Square2Circle
Fondz9/15/20192Circle8.75Star
Fondz9/15/20191Star2Star
Fondz9/15/20193Square2Star
Fondz9/15/20192Circle8.75Square
Fondz9/15/20191Star2Square
Fondz9/15/20193Square2Square

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:

3.JPG

Step2:

Expand the Project name and rename this column.

4.JPG

Result:

5.JPG

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors