Help! - Convert this measure to a calculated column to use in a lookup function.

[Anonymous]

I have attached the measure and the Ranked table here.

How do I convert this measure into a column. my aim is to use the column for a lookup function.

Any help is appreciated.

THanks,

Anand!

[lc_finance]

Hi @Anonymous ,

you can try the following code. I checked it and it works for me

Let me know if it's also ok for you

LC

Measure 3 = 

RANKX(
    FILTER(
        'Assign',
        'Assign'[Req Num]= EARLIER(Assign[Req Num])
    ), 
    'Assign'[index],,ASC
)

[Anonymous]

Thank you so much! That worked fine.

But my ultimate goal would be to use that column for a lookup function. I can't, however use that. were you able to use that column in a lookup function after applying the filters?

[v-lili6-msft]

hi, @Anonymous 

First, you should know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Best Regards,

Lin

[Anonymous]

Thank you for your reply!

Then, how can we lookup for a value that changes dynamically?

Looking forward to hearing from you

Thanks,

Anand!

[lc_finance]

Hi @Anonymous ,

Can you share more on what you would like to obtain with the LOOKUPVALUE? possibly with an example

LC

[Anonymous]

Thank you for your reply!

For example -

I should lookup for the start date that corresponds to the second  rank after selecting the req number through the filters.

I wil then use that start date for another calculation.

Here - I'm selecting the req number 3:

So if I'm looking for 2nd rank I should get the value of Jan 10.

Looking forward to hearing from you.

Thanks,

[lc_finance · Accepted solution]

Hi @Anonymous ,

below is the lookupmeasure using calculate, it filters the 2nd rank. 

If you filter Req Number 4, it will show March 2nd, which is the 2nd rank for Req Number 4.

Screenshot below

lookupmeasure = 
CALCULATETABLE(
    VALUES('Assign'[Start Date]),
    'Assign'[rank measure]=2
)

[Anonymous]

Thank you so much. That worked perfectly fine.