Distinct count of unique values filtering by ID and Date

[Anonymous]

Good morning to everybody,

I will explain my situation. In my dataset, I have records of connections to an app. I want to know which users have been loyal and which have not. A loyal user is one who has entered in the app 5 different days during the last month.

I am currently trying to make a dichotomous column that tells me if the user is loyal or not.

I have a measure for it (which works fine) which is as follows:

Loyal users =

Var usersbydate = SUMMARIZE(Table, Table[userid], "occurrences", IF(CALCULATE(DISTINCTCOUNT(Table[Date]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -30, DAY))>=5, 1.0))

Var SumLoyal = SUMX(usersbydate, [occurrences])

RETURN COALESCE(SumLoyal, 0)

The problem is that doing it in a column is impossible for me, since you must filter by the last 30 days and then make a distinct count of the days filtered by the user ID. I need a calculated column because I want to use it for filtering and drill through. The only thing I have achieved has been to obtain if they have entered 5 different days, but not to do so for the last 30 days.

Any idea how to do it?

Thanks in advance for your attention.

Greetings,

[v-stephen-msft]

Hi @Anonymous ,

Do you want to show only loyal users of the last month in the visual?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

No, I needed to do a Drill Throught, so that's why I was trying to do it with DAX. The only solution that I found is make a Calculated Table with a SUMMARIZE() function and link it in the model. Then I can use RELATED() to find the loyal users. 

[amitchandak]

@Anonymous , A column can be dynamic based on slicer value. So you have to use a measure. You can use the same measure a visual level filter on drill-down page