CODEWITHSUNDEEP
powerbidax
Simon Breton
Simon Breton

Reputation: 2876

count distinct value filtered based on date diff measure

I have the following dataset

user_id, login_date
111, 01/02/2021
222, 02/15/2021
444, 02/20/2021
555, 01/15/2021
222, 03/10/2021
444, 03/11/2021

I want to count of the number of unique active user_id in the last 90 days based on the max date of my date slicer. I'd like to solve this without using filters. This also needs to be dynamic as max date can be change from the date slicer.

From what I have understand so far I will need to evaluate for each row if the date difference between the current date of the row and the max date of the slicer is less than 90 days. Then for all the rows where the date diff is less than 90 days I will want to count the distinct number of users.

so basically I will have three layer in my final formula

I've tried many approach and formula. I think that this one is closed to something that could work:

Measure test  = CALCULATE(SUMX(DISTINCT(mytable[user_id]),filter(mytable,DATEDIFF(SELECTEDVALUE(mytable[login_date]),[Max range date],DAY)>90)))

this formula return me the following error :

 The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I've also tried applying a if statement to output the date diff as 0 and 1 and hopefully being able to sum this for each unique id with something like this

SUMX( VALUES(my_table[user_id]), IF(DATEDIFF(SELECTEDVALUE(mytable[login_date]),[Max range date],DAY)>90,0,1)

anyway I'm kind of stuck. hopefully my question is clear enough.

Upvotes: 0

Views: 1371

Answers (1)

AntrikshSharma
AntrikshSharma

Reputation: 661

You will have to create a disconnected table from which you will use the date column in the slicer, I have prepared a power BI file which included 2 very common scenarios, I hope that helps you.

File - Simon.pbix

Screenshot of the report - https://ibb.co/xjrjVv5

Screenshot of the model - https://ibb.co/Ws1z8D7

DAX Code -

for simon =
IF (
    ISINSCOPE ( simon[Login Date] )
        || ISINSCOPE ( simon[User ID] ),
    VAR LastVisibleDate =
        CALCULATE (
            MAX ( 'Simon Date Table'[Login Date] ),
            ALLSELECTED ( 'Simon Date Table' )
        )
    VAR CurrentDate =
        MAX ( simon[Login Date] )
    VAR TimeJump = 90
    VAR Result =
        CALCULATE (
            DISTINCTCOUNT ( simon[User ID] ),
            simon[Login Date] <= LastVisibleDate,
            simon[Login Date] > LastVisibleDate - TimeJump,
            ALLSELECTED ( simon )
        )
    RETURN
        Result
)

second version:

for simon 2 =
IF (
    ISINSCOPE ( simon[Login Date] )
        || ISINSCOPE ( simon[User ID] ),
    VAR LastVisibleDate =
        CALCULATE (
            MAX ( 'Simon Date Table'[Login Date] ),
            ALLSELECTED ( 'Simon Date Table' )
        )
    VAR CurrentDate =
        MAX ( simon[Login Date] )
    VAR TimeJump = 90
    VAR Result =
        IF (
            CurrentDate <= LastVisibleDate,
            CALCULATE (
                DISTINCTCOUNT ( simon[User ID] ),
                simon[Login Date] <= CurrentDate,
                simon[Login Date] > CurrentDate - TimeJump,
                ALLSELECTED ( simon )
            )
        )
    RETURN
        Result
)

Upvotes: 1

Related Questions