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

[Majad_Chowdhury]

Hi there,

I'd like a bit of help in how to create a calculated column to give us the following information.

Assume the dataset has no repeat rows.

Name	Rank	Stage
Mr. Chandak	2	Interest
Mr. Miguel	2	Negotiation
Mr. Ibendlin	2	Negotiaion
Mr. Tamer	2	Interest
Mr. Chandak	1	Negotiation
Mr. Miguel	1	Complete
Mr. Ibendlin	1	Negotiation
Mr. Tamer	1	Complete

I want to be able to retrieve the stage per name for when rank is 1 and rank is 2. So in a power bi visual, the final result should look like this.

	Stage Last Week (rank 2)	Stage This Week (rank 1)
Mr. Chandak	Interest	Negotiation
Mr. Miguel	Negotiation	Complete
Mr. Ibendlin	Negotiaion	Negotiation
Mr. Tamer	Interest	Complete

I've managed to do this using a measure, however i cannot use the measure results as a filter thus need it to be a column.

The formula i used for measure was the following: 

stagelastweek = MAXX(FILTER('Dataset',[Rank]=2),'Dataset'[Stage])

stagethisweek = MAXX(FILTER('Dataset',[Rank]=1),'Dataset'[Stage])

Let me know if you need any more information gurus. many thanks!

[v-yueyunzh-msft · Accepted solution]

Hi, @Majad_Chowdhury 

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