Forum Discussion
Helper IIn a column, calculate a value per group
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
Super Userthat sounds rather restrictive and not really in the spirit of Power BI user interactivity. Are you sure you need Power BI for this?
Helper IAt 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" columnsI 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?