Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Problem with filter measure

Hi all,

I have a table where I have logins, one row per each day of activity (possible to have more than 1 row in a month).

Each row has a reference (Registration_Date) of when the user was created, so the same value is repeated for each login.

 

I made a measure to show how many new users I had each month, based on the Registration_Date.

 

I get the totals right in a matrix but when I click on one value to filter another table I get those users but also all the ones with a Registration_Date earlier than that date. How can I fix it?

 

Here the DAX measure 

 

VAR currentusers = VALUES(PENDO_Visitors[Visitor ID])
VAR currentDate = MIN('calendar'[Date])

VAR pastusers = CALCULATETABLE(VALUES(PENDO_Visitors[Visitor ID]), 
    ALL('calendar'[Date].[Month],'calendar'[Date].[MonthNo],'calendar'[Date].[Year])
    , PENDO_Visitors[Registration_Date]<currentDate)

VAR NewUsers = EXCEPT(currentusers,pastusers)

RETURN COUNTROWS(NewUsers)

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Join you date table with Registration_Date or have inactive join

     

    Measure with inactive join

     

    users=

    calculate(Distinctcount(PENDO_Visitors[Visitor ID]),  userelationship(Table[Registration_Date], Calendar[Date]))

     

     

    LTD before 1 =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max = eomonth(_max1 ,-1)
    var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    This month =var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     

    New = Countx( VALUES(PENDO_Visitors[Visitor ID]), if(isblank([LTD before 1]) && not(isblank([This Month])), [Visitor ID], blank()))

     

     

    Refer , very similar


    Customer Retention Part 5: LTD Vs Period Retention
    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497