In a column, calculate a value per group

[cris1196]

Hi All. I want to reduce the amount of columns that I currently have. My table has the following format:

Date		User		Store		Cohort Year		Rank		Select Store
01/01/2021		25		84		202101		1		79
02/02/2021		25		79		202102		2		79
05/02/2021		25		2		202102		3		79
07/05/2021		25		2		202105		4		79
03/08/2022		25		7		202208		5		79

Where

Rank =
var user = Table[User]
return
RANKX(FILTER(ALLSELECTED(Table), Table[User]=user ), Table[Date],, ASC, Dense

And

Select Store= 
var user = Table[User]
return
CALCULATE(MIN(Table[Store]), FILTER(ALLSELECTED(Table), Table[User]=customer_ && Table[Rank]=2))

That is, I want to know in which Store the user made his purchase number x. In this case it was purchase number two, but if I want to see where he made his fourth purchase, I change "Select Store" Rank = 4 and I would get 2 instead of 79.

Now I want the same thing, but I don't want to have the "rank" column anymore, that is, I want this:

Date		User		Store		Cohort Year		Select Store
01/01/2021		25		84		202101		79
02/02/2021		25		79		202102		79
05/02/2021		25		2		202102		79
07/05/2021		25		2		202105		79
03/08/2022		25		7		202208		79

But I can't get it to bring me the minimum value for all the rows per user, only in one 😬

[lbendlin]

In this case it was purchase number two, but if I want to see where he made his fourth purchase

How are you specifying this?  Via "What-If?" parameters?

[cris1196]

Hello, thanks for answering. No, just change Table[Rank]=2 to Table[Rank]=4 or, I create another "select cohort" column with Table[Rank]=4.

[lbendlin]

that sounds rather restrictive and not really in the spirit of Power BI user interactivity. Are you sure you need Power BI for this?

[cris1196]

At least in my case, yes. I have three ranks columns and three "Select Cohort" columns (which, now that I realize it, should be called "Select Store"...), each for a particular purpose.

And from there comes the cause of my question: if there is any way to achieve, in this case, not needing the use of the "Rank" column

Currently I keep trying some things but without success 😑

[lbendlin]

and three "Select Cohort" columns

I don't understand that part.  Your sample data only has one (and I think that is more appropriate).  I don't understand what you are trying to model, but it doesn't sound like stuff you would do in Power BI.

[cris1196]

This is what i have:

Date		User		Store		Cohort Year		Rank_1		Select Store_1		Rank_2		Select Store_2
01/01/2021		25		84		202101		1		79		1		2
02/02/2021		25		79		202102		2		79		2		2
05/02/2021		25		2		202102		3		79		3		2
07/05/2021		25		2		202105		4		79		4		2
03/08/2022		25		7		202208		5		79		0		2

There's more, but for the context of my question, the columns shown above were what matter, since the others don't affect anything (in fact, they depend on the Select Store column, so it doesn't seem to me that it is not necessary to show them, since otherwise there would be a lot of unnecessary data)

The columns Rank_1/Rank_2 and Select Store_1 and Select Store_2, have the same calculation that I put above, just with another equality.

The only thing I want is, for a matter of having fewer columns if possible, is to remove the rank column and get the same result.

Example:

With the measurements above, one of the first things I tried was this:

Select Sttore_1= 
var user = Table[User]
var rank_ = RANKX(FILTER(ALLSELECTED(Table), Table[User]=user ), Table[Date],, ASC, Dense
return
CALCULATE(MIN(Table[Store]), FILTER(ALLSELECTED(Table), Table[User]=customer_ && rank_=2))

For obvious reasons that I later realized, that only brings me this:

Date		User		Store		Cohort Year		Select Store_1
01/01/2021		25		84		202101
02/02/2021		25		79		202102		79
05/02/2021		25		2		202102
07/05/2021		25		2		202105
03/08/2022		25		7		202208

(and i want "79" in all rows for the user 25)

I just want to know if there is a way to get what I'm asking for, without having to have the "rank" column calculated. And I'm only asking for the one in this column, because if I can get a solution for it, the logic would be the same for all the others.

[lbendlin]

You can do that by using REMOVEFILTERS  but I still don't understand how you decided to pick store 79.  What's the criteria?  Is that something you do as the developer of the report, or something that the user of the report will do dynamically?

[cris1196]

It is long to explain, but in a few words I need for some later calculations,:

var1 = give me the second store per user
var2 = give me the third store per user

and then other measures are applied that are irrelevant, but in a nutshell, I need to know for other calculations.

What I currently have works perfectly for me, I just want to reduce the number of columns I have (if possible).

I'm trying the removefilters in every place I can think of in the calculate but still getting the same result 😔

[v-yueyunzh-msft · Accepted solution]

Hi , @cris1196 

According to your description, you want to move the rankx column logic to the [Select Sttore] column.

Here are the steps you can refer to :
(1)This is my test data:

(2)We can click "New Column" and enter this:

Select Sttore_1 = 
var user =[User]
var _t =ADDCOLUMNS('Table',"rankx" , RANKX(FILTER('Table', 'Table'[User]=user ), 'Table'[Date],, ASC, Dense))
var _t2= FILTER(_t ,[User]=user && [rankx]=2)
return
MINX(_t2,[Store])

(3)Then we can meet your need , the result is as follows:

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