Forum Discussion

cris1196's avatar
cris1196
Icon for Helper I rankHelper I
3 years ago
Solved

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 ...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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's avatar
cris1196
Icon for Helper I rankHelper I
3 years ago

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's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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