Retrieve a value from a column based on the value of another column on the same row (within group)

[Fabio74]

Hi all, I have a (I think) pretty basic problem but I'm totally new to DAX and I'm really struggling to understand its basic concepts.

I've tried to look on the web at solutions for problems similar to mine, but I keep on hitting walls.

I have a table with a similar content (sorry for the format) 

Name Rank List of Items Desidered Outcome
Roger 2 Apples
Roger 3 Oranges
Roger 5 Bananas Bananas
Roger 6 Strawberries
Roger 7 Potatoes
Roger 1 Bananas Bananas
Roger 4 Cherries
Mike 2 Pineapples
Mike 3 Tomatoes
Mike 5 Cherries
Mike 1 Apples Apples
Mike 7 Oranges
Mike 6 Bananas
Mike 4 Apples Apples

I'd like to find the name of the item whose Rank is equal to 1 (for each person) and then write the name of the item in my calculated column (Desidered Outcome) whenever it matches the name in the List of Items column.  I know it might seem dumb, but it serves a purpose.

I've tried with LOOKUPVALUE but since the Rank column doesn't have unique values it returns an error, so I know I have to use a FILTER. I've tried to use different combinations of CALCULATE and FILTER but there's something basic I'm missing, because I can't make them work

Thanks for all the help!

[Jihwan_Kim]

Hi, @Fabio74 

Please try the below whether it suits your case.

Result Calcuated Column =

VAR currentname = 'Table'[Name]

VAR findrankoneitem =

MAXX (

FILTER ( 'Table', 'Table'[Name] = currentname && 'Table'[Rank] = 1 ),

'Table'[Items]

)

RETURN

IF ( 'Table'[Items] = findrankoneitem, 'Table'[Items], BLANK () )

Hi, My name is Jihwan Kim.

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

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Fabio74]

Thank you @Jihwan_Kim, I'll try your solution too and hopefully in the next (not to far I hope) future I'll be able to tell the difference between all your suggestions.

Have a nice day!

[Fowmy · Accepted solution]

@Fabio74 

Use the ALLEXCEPT to filter by Person then get the item for rank 1.

Desidered Outcome = 

VAR __item = 
    CALCULATE(
        MAX('Table'[Item]),
        'Table'[Rank] = 1,
        ALLEXCEPT('Table','Table'[Person])
    )
return
    IF(__item = [Item] , __item)    

[Fabio74]

Dear @Fowmy , it works like a charm!

I still don't understand why @selimovd solution worked for me in one case and not in the other, but for the time being I'll accept it as it is. 

Thank you so much, I'm losing hours one these kind of things 🙂

[selimovd]

Hey @Fabio74 ,

try the following measure:

Outcome = 
VAR vCurrentName = MyTable[Name]
VAR vResult = CALCULATE( MAX( MyTable[List of Items] ), MyTable[Name] = vCurrentName, MyTable[Rank] = 1 )
RETURN
  vResult

I think you should take a look about row and filter context:

Row Context and Filter Context in DAX - SQLBI

In the variable vCurrentName I save the name of the row context. That is basically the value of the name column in the current row.

Then I use a CALCULATE to get the item. Be aware that CALCULATE is triggering context transition. I set the filter on the person in the current row and Rank 1. Like this you should get the result you wish.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Fabio74]

Dear @selimovd , thank you so much for your quick reply.

I've applied your formula in the test table I've provided above and it works just fine. But if I copy the same formula in my real table, the correct Item description is written only on the row whose Rank is = to 1.

There obviously must be something different between my actual table and the example one, but for the life of me I can't figure out what.  In terms of logic it looks (to me) like the exact same thing ... so frustrating.

I'll get back to it with a clearer mind. Thanks for now!