topic Re: Column Filter in DAX Commands and Tips

Column Filter

mweber87 — Mon, 31 Oct 2022 17:00:54 GMT

I have a labor cost table. One of the columns is job_id. In this column there are 5259 distinct values. And each job_id has multiple entries (rows) with data. I am trying to get the labor total per week per job id. Is there a way to group the identical job_id so that I can sum the total of each. I know I could use DAX filter and filter out each job_id individually. However since there are so many records is there an easier time saving way to do this?

Re: Column Filter

Anonymous — Mon, 31 Oct 2022 16:46:34 GMT

hello you could try and use the allexcept(table,table[job_id])

Re: Column Filter

mweber87 — Mon, 31 Oct 2022 16:59:37 GMT

I think I may have worded my question wrong. I do not want to get rid of the whole column. I want to some how be able to add up the transaction total for each job id per week. Each job_id has multiple entries.

Thanks!

Re: Column Filter

Anonymous — Mon, 31 Oct 2022 17:02:53 GMT

ok can you please sample the data?
and i didnt quite understand your question do you mean you want to count the job id per week?

Re: Column Filter

mweber87 — Wed, 02 Nov 2022 16:47:47 GMT

Here is a sample set: This one JOB_ID has muliple Transaction_Amount entries. I want to be able to sum all of the transaction_amounts for each Job_ID. There are about 5000 different JOB_ID. My end result is to calcualte the labor per week per job id.

Transaction_Date	JOB_ID	ETYPE	Transaction_Amount
Thursday, August 02, 2012	16246	LAB	878.61
Thursday, August 09, 2012	16246	LAB	2912.62
Thursday, August 16, 2012	16246	LAB	6222.66
Thursday, August 23, 2012	16246	LAB	5111.06
Thursday, August 30, 2012	16246	LAB	3163.87
Thursday, September 06, 2012	16246	LAB	2085.32
Thursday, September 13, 2012	16246	LAB	1652.74
Thursday, September 20, 2012	16246	LAB	2092.15
Thursday, September 27, 2012	16246	LAB	2142.2
Thursday, October 04, 2012	16246	LAB	2313.74
Thursday, October 11, 2012	16246	LAB	1821.57

Re: Column Filter

Anonymous — Thu, 03 Nov 2022 00:01:06 GMT

hello mweber87 ,
please give this a try:

total = CALCULATE(SUM(powerbi[Transaction_Amount]),ALLEXCEPT(powerbi,powerbi[JOB_ID],powerbi[week]))

if this helps, kindly accept as solution

Re: Column Filter

mweber87 — Thu, 03 Nov 2022 13:45:42 GMT

Yes that does work however there are 5,259 different job_id's that I need to total. And I was hoping there was a way I could group the job id's without having to write an expression for each job id.

Re: Column Filter

Anonymous — Thu, 03 Nov 2022 14:10:22 GMT

Hello mweber87 can you please explain it more

Re: Column Filter

mweber87 — Thu, 03 Nov 2022 14:40:57 GMT

The data set contains a job id, this job has a labor cost weekly until the job is finished (contruction work) There are 5,259 different jobs and each job has a labor cost assoicated with it each week. I need to figure out how much each job's labor cost is per week. Ultimately, I have to create a visual that shows the labor cost per week per job for the life of the job.