Power BI Group By Using DAX Calculated column

[rachaelwalker]

Hello, I am looking for a DAX formula that must be a calculated column due to the visual requirements. This is for a scheduling calendar. I have the Start of Week, Technician, and the projects they are assigned to for that week seperated by a comma. I only want one row per techncian per week. I figured it out using a measure but I need it to be a column for the visual to work. When I paste my measure formula into calculated column it no longer works. Is there a summarize or group by formula that I could use?

(i do not want to do this in power query because I want to maintain the relationship to a project details table)

Measure is below

Jobs = CONCATENATEX(VALUES('Schedule Details'[Job Number]), 'Schedule Details'[Job Number], ", ")

[SamWiseOwl · Accepted solution]

Hi @rachaelwalker 

When you have a visual it automatically applies filters based on the columns in the visual.

In tables this doesn't occur and we have to create those filters.

You could create a column in the same table or create a new calculated table.

I've assumed you are staying in the same table for now.

Schedule columns =

var personname = 'Schedule Details'[Name] --Capture the person

var datestar = 'Schedule Details'[Date Start of week] --Capture the date

return

Calculate( --apply filters
CONCATENATEX(VALUES('Schedule Details'[Job Number]), 'Schedule Details'[Job Number], ", ")
,All('Schedule Details') --Return all rows

,'Schedule Details'[Name] = personname ,  'Schedule Details'[Date Start of week] = datestar)
--filter to the original rows person and date

My data is a little sillier than yours...

[SamWiseOwl · Accepted solution]

Hi @rachaelwalker 

When you have a visual it automatically applies filters based on the columns in the visual.

In tables this doesn't occur and we have to create those filters.

You could create a column in the same table or create a new calculated table.

I've assumed you are staying in the same table for now.

Schedule columns =

var personname = 'Schedule Details'[Name] --Capture the person

var datestar = 'Schedule Details'[Date Start of week] --Capture the date

return

Calculate( --apply filters
CONCATENATEX(VALUES('Schedule Details'[Job Number]), 'Schedule Details'[Job Number], ", ")
,All('Schedule Details') --Return all rows

,'Schedule Details'[Name] = personname ,  'Schedule Details'[Date Start of week] = datestar)
--filter to the original rows person and date

My data is a little sillier than yours...

[rachaelwalker]

Thank you so much!! This worked for me and I appreciate the helpful comments explaining it as well!!

[SamWiseOwl · Accepted solution]

@rachaelwalker if you wanted a table it would be something like this.
Home - New Table 

New Table =

SUMMARIZECOLUMNS('Schedule Details'[Name],  'Schedule Details'[Date Start of week] ,"Job list",

    CONCATENATEX(values('Schedule Details'[Job Number]), [Job Number],", ")

)

[rachaelwalker]

Is there any particular reason I would want to use a table over a column? This was helpful as well. I ended up using a column but I have this formula saved. 

[SamWiseOwl]

The column can't remove the duplicate rows, since each job list is separate. Using the grouped table created by summarize would.