Power BI - How to select a cell from column A based on a value in column B

[yao_xiao]

Hi everyone,

Does anyone know how to select the value in a column based on the value in another column? 

For example, I have

A	B
Amy	1
Bob	3
Charlie	5
David	4
Ethan	6
Frank	2

How to get "Ethan" when I pick 6 in the column B filter, or get "Charlie" when I pick 5 in the filter?

Thank you!

[yao_xiao · Accepted solution]

Hi @v-agajavelly, @rohit1991, @danextian,

Thank you for your responses! 

I actually haven't described the problem in enough details. There is another table that I have that gives ratings associated with each name. And my intention is to be able to calculate the average rating for the person two order above the selected person.

But I have figured it out using this DAX measure:

Average Rating_Previous =

VAR Order_Selected = MAX(table1[Order])

VAR Order_Previous = Order_Selected - 2

RETURN

    CALCULATE(

        AVERAGE('Table2'[Rating]),

        Table1[Order] = Order_Previous,

        REMOVEFILTERS(Table1[Name])

    )

Again, thank you for all your help, and hope that this post helps the others too 🙂

[v-agajavelly]

Hi @yao_xiao 

Thanks alot @danextian  and @rohit1991 for your time and  appreciate you both shared clear and practical answers.

The slicer on column B is doing exactly what’s expected filtering down the table to the selected row, so showing "Ethan" when 6 is picked works straight out of the box if you're using a table or matrix visual. And for a cleaner display (like on a card visual), use the bellow DAX measure.

SelectedName = SELECTEDVALUE(TableName[A])

It will display useful when you just want to show the result without the full table.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thank you,
Akhil.

[rohit1991]

Hi @yao_xiao 

Please follow the steps below:

1. Put B in a slicer and turn Single select = On.
2. Create a measure and show it in a Card.

If B is unique (one row per B):

Selected Name =
VAR k = SELECTEDVALUE ( Data[B] )
RETURN IF ( ISBLANK(k), BLANK(), LOOKUPVALUE ( Data[A], Data[B], k ) )

If B can have duplicates (return all matching names):

Selected Name (multi) =
VAR k = SELECTEDVALUE ( Data[B] )
RETURN
IF (
    ISBLANK(k),
    BLANK(),
    CALCULATE ( CONCATENATEX ( VALUES ( Data[A] ), Data[A], ", " ), Data[B] = k )
)

[danextian]

Hi @yao_xiao 

When you select 6 in a slicer that's using Column B, the rows are filtered to just that row and only Ethan should show up.  What do you intend to do with the value?