cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## In a column, calculate a value per group

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]returnRANKX(FILTER(ALLSELECTED(Table), Table[User]=user ), Table[Date],, ASC, Dense`

And

`Select Store= var user = Table[User]returnCALCULATE(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 😬

1 ACCEPTED SOLUTION
Community Support

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

9 REPLIES 9
Super User
``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?

Helper I

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.

Super User

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

Helper I

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 😑

Super User
``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.

Helper I

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, DensereturnCALCULATE(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.

Super User

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?

Helper I

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 😔

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.