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

Trying to recreate a calculated column in a measure

Hello. Sorry for the nondescript title, I couldn't think of anything else to put. I'm going to try to explain my problem in detail, because maybe you can come up with a better idea than what I'm asking in this post:

I have this table:

 

Date (dd-mm-yyyy)||User||Region||Region_America||Country||Cohort Year||Firt Order America||Rank
01/03/2021||Marx||Europe||False||Italy||202103||No||0
01/06/2021||Marx||Asia||False||China||202106||No||0
01/09/2021||Marx||America||True||Brazil||202109||Yes||1
05/09/2021||Marx||America||True||Bolivia||202109||No||2
07/09/2021||Marx||America||True||Argentina||202109||No||3
09/12/2021||Marx||Europe||False||Spain||202122||No||0

 

This table is a list of users who have purchased, showing the region, country, etc.

The "Rank" column tells me how many in ascending order, how many times a user bought within 30 days, biased by region:

 

Rank = 
var user_ = Table[User]
var min_date_ = CALCULATE(MIN(Table[Date]), FILTER(ALLSELECTED(Table), Table[User]=user_ && Table[First Order America]=TRUE()))
var between_30_Days = IF(Table[Date]<=min_date_+30, TRUE(), FALSE())
RETURN
IF(between_30_Days=TRUE() && Table[Region_America]
, RANKX(FILTER(ALLSELECTED(Table), Table[Region_America] && Table[User]=customer_ && between_30_Days)
,Table[Date]
,
,ASC
,Dense)
,0)

 

 

 

 

So, I want to know how many users had two purchases in 30 days, that is, how many users have rank=2. I do that with this:

 

 

Measure =
var cohort_ = SELECTEDVALUE(Table[YearMonth])
var column_ = SELECTEDVALUE(Date[Day])
RETURN
CALCULATE
(DISTINCTCOUNT(Table[User])
,FILTER(ALLSELECTED(Table), Table[Cohort Year]=cohort_ && Table[Rank]=2 && Related(Table[Day])=column_)

(there are some columns that are not included in the table above, it was already too long)

So far so good, my problem is the following: if in the visual part I want to add a slicer/filter, for example, by country, the logic I used will not work. Example: I insert a slicer by country and I select "Bolivia". My measurement would be counting the following row:

05/09/2021 Marx America True Bolivia 202109 No 2

 

 

Which is wrong. This is not indicating to me that the user bought twice, in 30 days, in Bolivia, but it still counts .

So, since a column cannot be dynamic and depend on a "selectedvalue" (or at least that's what I understand), my idea would be to recreate the "rank" column in the measure "measure", so I can filter by country correctly and if in In the future I want to add other columns (for example by gender, age, province, etc.), and I seek to filter them by those columns, I wouldn't have any problem.

Is this possible? I couldn't think of a way to make what I say work

 

Sorry for the long post, I wrote the measures I used because maybe you can think of a better way to face the situation, without having to "recreate" the rank column in a measure.

 

Thanks 🙂

 

1 ACCEPTED SOLUTION

Hi , @cris1196 

Thanks for your quick response!

I test your sample data in my side .

For this , i think you need to re-create the measure in the matrix visual both.

I create two measures like this:

Measure rankx = var _cur_user = MAX('Table'[User])
var _mindate = MINX(FILTER( ALLSELECTED('Table') ,'Table'[User] =_cur_user && 'Table'[First Buy Europe]=TRUE()),[Date])
var _cur_date = MAX('Table'[Date])
var _region_america=SELECTEDVALUE('Table'[Region Europe2])
var _rankx = RANKX( FILTER(ALLSELECTED('Table'),'Table'[User]=_cur_user && 'Table'[Region Europe2]=TRUE()) , CALCULATE( MAX('Table'[Date])),,ASC,Dense)
return
IF(_cur_date<=_mindate+30 && _region_america=TRUE(),_rankx,0)
Measure Count Test = 
var cohort_week = SELECTEDVALUE('Date'[Cohort Month])
var days = SELECTEDVALUE('Table'[Datediff Europe])
var _t =SUMMARIZE( 'Table' , 'Table'[User] , 'Table'[Date] , 'Table'[First Buy Europe],'Table'[Region Europe2] , "rankx" , [Measure rankx])
var _t2 = SELECTCOLUMNS(FILTER( _t ,[rankx]=2),"user" , [User])
 return
 COUNTROWS( DISTINCT(_t2))
// CALCULATE(DISTINCTCOUNT('Table'[User]),FILTER(ALLSELECTED('Table'), 'Table'[Cohort Month First Order Europe]=cohort_week && 'Table'[Rank Europe Buy]=2 && 'Table'[Datediff Europe]=days))

 

Then we can get the result as follows:

vyueyunzhmsft_0-1675406154899.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

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

Hi , @cris1196 

Your understanding is correct, our computed columns cannot be affected by slicers. For now, your expectation is to rewrite your rankx columns using the measure method.

Here are the steps you can refer to :
(1)This is my test data:

vyueyunzhmsft_0-1675318777773.png

(2)We can create a measure like this:

Measure = var _cur_user = MAX('Table'[User])
var _mindate = MINX(FILTER( ALLSELECTED('Table') ,'Table'[User] =_cur_user && 'Table'[First Order America]="Yes"),[Date (dd-mm-yyyy)])
var _cur_date = MAX('Table'[Date (dd-mm-yyyy)])
var _region_america=SELECTEDVALUE('Table'[Region_America])
var _rankx = RANKX( FILTER(ALLSELECTED('Table'),'Table'[User]=_cur_user && 'Table'[Region_America]=TRUE()) , CALCULATE( MAX('Table'[Date (dd-mm-yyyy)])),,ASC,Dense)
return
IF(_cur_date<=_mindate+30 && _region_america=TRUE(),_rankx,0)

 

(3)Then we can put the measure on the visual and meet your need:

vyueyunzhmsft_1-1675318882785.png

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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

 

Thanks for answering. Unfortunately it didn't work for me. Here is my sample data with randomly generated data:

Link



To explain myself better: let's focus on row 202204 and column 28: according to my measure there are 30 users who agree with my measure, which is correct.

Now let's filter by Store_ID = 5, using the Slicer: now we have 7 values ​​for those coordinates

cris1196_0-1675401851069.png

 

Which is wrong. One of the seven users is "017fc519b5170bcc", who appears because he actually meets the requirements of my measure, the problem is that what I want to indicate with Slicer Region = Europe and Slicer Store_Id = 5, is "this user bought 2 times in region = Europe, Store_Id = 5".

That is, the final result should be 6, not 7-->other users, if we look closely, really bought twice, in less than 30 days, in Store_Id = 5, but our user "017fc519b5170bcc", bought first in 207 and then in 5.

To make it better understood, an easy solution to this (but impossible), would be, for the "Rank Europe Buy" calculated column, be filtered by the slicer and then do the calculation, but since there are no dynamic columns filtered by the slicer, I have to make a measure that fulfills the same function.


With the calculation that you gave me, unfortunately I couldn't make it work 😔


I know it could be done by creating more columns, but I would have to create many more (one for each Store_ID) which is... well, not optimal. I don't even know if what I'm asking is possible, I'm new to PBI and sometimes I think I want to do things that aren't currently possible 😅

PS: Excuse my grammar, I understand English well but I make several grammatical errors

Hi , @cris1196 

Thanks for your quick response!

I test your sample data in my side .

For this , i think you need to re-create the measure in the matrix visual both.

I create two measures like this:

Measure rankx = var _cur_user = MAX('Table'[User])
var _mindate = MINX(FILTER( ALLSELECTED('Table') ,'Table'[User] =_cur_user && 'Table'[First Buy Europe]=TRUE()),[Date])
var _cur_date = MAX('Table'[Date])
var _region_america=SELECTEDVALUE('Table'[Region Europe2])
var _rankx = RANKX( FILTER(ALLSELECTED('Table'),'Table'[User]=_cur_user && 'Table'[Region Europe2]=TRUE()) , CALCULATE( MAX('Table'[Date])),,ASC,Dense)
return
IF(_cur_date<=_mindate+30 && _region_america=TRUE(),_rankx,0)
Measure Count Test = 
var cohort_week = SELECTEDVALUE('Date'[Cohort Month])
var days = SELECTEDVALUE('Table'[Datediff Europe])
var _t =SUMMARIZE( 'Table' , 'Table'[User] , 'Table'[Date] , 'Table'[First Buy Europe],'Table'[Region Europe2] , "rankx" , [Measure rankx])
var _t2 = SELECTCOLUMNS(FILTER( _t ,[rankx]=2),"user" , [User])
 return
 COUNTROWS( DISTINCT(_t2))
// CALCULATE(DISTINCTCOUNT('Table'[User]),FILTER(ALLSELECTED('Table'), 'Table'[Cohort Month First Order Europe]=cohort_week && 'Table'[Rank Europe Buy]=2 && 'Table'[Datediff Europe]=days))

 

Then we can get the result as follows:

vyueyunzhmsft_0-1675406154899.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

Wow it works! It would never have occurred to me to use "summarize" to achieve this.

Now I'm going to study what logic you did 😁

Thanks you!

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.