Trying to recreate a calculated column in a measure

[cris1196]

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 🙂

[v-yueyunzh-msft]

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:

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

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

[cris1196]

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

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

[v-yueyunzh-msft · 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:

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

[cris1196]

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!