Skip to main content
cancel
Showing results for 
Search instead 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

Reply
cris1196
Helper I
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]
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 😬

1 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:

vyueyunzhmsft_0-1675651861940.png

(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:

vyueyunzhmsft_1-1675651891986.png

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

 

View solution in original post

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

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.

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

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 😑

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.

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.

 

 

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?

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 😔

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:

vyueyunzhmsft_0-1675651861940.png

(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:

vyueyunzhmsft_1-1675651891986.png

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.