How to retrieve a value using a filter (calculated Column)

Hi, Anonymous 

Thanks for your sample test data first!

According to your description, you want to change the measure to calculated columns due to you want to make the value as a filter.

First , if you just create two calculated Columns in your table like this:

stagethisweek Column = IF( [Rank]=1 , [Stage])

stagelastweek Column = IF([Rank]=2 , [Stage])

The result is as follows :

When we put this in the visual it will return this :

I think it may not meet your need, For your need , i think you need to create a calculated Table , like this:

Table = ADDCOLUMNS( CROSSJOIN( VALUES('Dataset'[Name]) , {"Stage Last Week (rank 2)","Stage This Week (rank 1)"}) , "test" ,
var _rank = [Value]  var _name = [Name] var _rank2 = MAXX( FILTER('Dataset','Dataset'[Name]=_name && 'Dataset'[Rank]=2 ) , [Stage])
var _rank1 = MAXX( FILTER('Dataset','Dataset'[Name]=_name && 'Dataset'[Rank]=1 ) , [Stage])
return IF(_rank="Stage Last Week (rank 2)" , _rank2 ,_rank1)
)

Then we can get this table:

We can put the fields we need on the matrix visual or slicer visual .

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly