topic Re: Value for a column within a table, filtering another column's value on the same table in DAX Commands and Tips

Value for a column within a table, filtering another column's value on the same table

nickc_innova — Fri, 30 Sep 2022 22:49:39 GMT

I've been at it for a few hours and searched around the forums - closest I have come to a similar problem/solution involves time intelligence and trying to find values via PREVIOUSYEAR().

I have a single table with names, values and column for previous name. I am trying to calculate the corresponding previous value.
The table looks something like this:

Note the far right column is what I am trying to return.

My current code looks like this:

Previous Worker Value =

VAR __prev_worker = 'Table'[Previous Worker]

VAR __jobid = 'Table'[Job id]

VAR __cur_val = CALCULATE(MAX('Table'[Value]))

VAR __prev_val = CALCULATE(__cur_val, ALLEXCEPT('Table', 'Table'[Job id]),

FILTER(

ALLNOBLANKROW ( 'Table'[Assigned Worker] ), 'Table'[Assigned Worker]=__prev_worker

)

RETURN

__prev_val

However, this is only returning values for me that are equal to the current value, not the previous worker's value for the corresponding job id. Like this:

Re: Value for a column within a table, filtering another column's value on the same table

tamerj1 — Sat, 01 Oct 2022 04:03:51 GMT

Hi nickc_innova
Please refer to attached sample file with the solution

Previous Worker Value = CALCULATE ( SUM ( 'Table'[Value] ), TREATAS ( { 'Table'[Previous Worker] }, 'Table'[Assigned Worker] ), ALLEXCEPT ( 'Table', 'Table'[Job Id] ) )

Re: Value for a column within a table, filtering another column's value on the same table

Jihwan_Kim — Sat, 01 Oct 2022 04:06:02 GMT

Hi,

I assume you want to create a calculated column.

Please check the below picture and the DAX formula.

Previous Worker Value CC = LOOKUPVALUE ( Data[Value], Data[Assigned Worker], Data[Previous Worker], Data[Job ID], Data[Job ID] )

Re: Value for a column within a table, filtering another column's value on the same table

nickc_innova — Mon, 03 Oct 2022 15:06:13 GMT

This results in an error. "A table of multiple values was supplied where a single value was expected".
For some Jobs, the assigned worker is reported more than once with different values. So, when looking up the previous worker, we get more than one result.
... need some way to summarize values where job and assigned worker or previous worker match

Re: Value for a column within a table, filtering another column's value on the same table

nickc_innova — Mon, 03 Oct 2022 15:13:19 GMT

tamerj1, thank you so much! Not only did you provide a perfect solution but you also introduced me to something new. I did not know about the TREATAS function. Very interesting.