Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nickc_innova
Advocate I
Advocate I

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

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:
nickc_innova_0-1664576966602.png

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:
nickc_innova_1-1664577427419.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @nickc_innova 
Please refer to attached sample file with the solution

1.png

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

 

 

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi,

I assume you want to create a calculated column.

Please check the below picture and the DAX formula.

 

Jihwan_Kim_0-1664597148559.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

tamerj1
Super User
Super User

Hi @nickc_innova 
Please refer to attached sample file with the solution

1.png

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors