Forum Discussion
Power BI Group By Using DAX Calculated column
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
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 dateMy data is a little sillier than yours...
rachaelwalker if you wanted a table it would be something like this.
Home - New TableNew Table =SUMMARIZECOLUMNS('Schedule Details'[Name], 'Schedule Details'[Date Start of week] ,"Job list",CONCATENATEX(values('Schedule Details'[Job Number]), [Job Number],", "))
5 Replies
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 dateMy data is a little sillier than yours...
rachaelwalker if you wanted a table it would be something like this.
Home - New TableNew Table =SUMMARIZECOLUMNS('Schedule Details'[Name], 'Schedule Details'[Date Start of week] ,"Job list",CONCATENATEX(values('Schedule Details'[Job Number]), [Job Number],", "))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.
Thank you so much!! This worked for me and I appreciate the helpful comments explaining it as well!!
