Forum Discussion
Helper IIn 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]
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 😬
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
- lbendlin
Super User
In this case it was purchase number two, but if I want to see where he made his fourth purchaseHow are you specifying this? Via "What-If?" parameters?
cris1196Hello, 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?
