Measure evaluating to blank

[amedvedev]

I have a table with the following columns:

[Tenant_ID] (text),

[Page_ID] (text),

[Pendo_visitor_ID] (text),

[NUM_MINUTES] (general),

[TIMESTAMP] (date/time) ex. 2022-05-18 2:00:00PM

[Pendo_visitor_ID] can only belong to one [Tenant_ID], and I only filter for one [Page_ID] at the visual level.

I am trying to calculate the following:

1. The number of active users at any given point in time. An active user is one where they have =>5 logins over a 45-day period.
2. The number of active tenant at any given point in time. An active tenant is one where there are => 5 active users.

To calculate 1, I took the following approach. I create a measure to:

1. Summarize a table filtering for a trailing 45 days with [Pendo_visitor_ID] as being the only column.
2. Added a column ([TimestampCount]) that counts the number or rows, which is essentially the same as counting [TIMESTAMP].
3. Filter this table for [TimestampCount]  => 5

This issue I am running into is as soon as I add the => 5 logic, the whole expression evaluates to a blank, if I remove that bit of logic, it correctly calculates the number of distinct users. My code is below for reference:

COUNTX(

    FILTER(

        ADDCOLUMNS(

            SUMMARIZE(

                CALCULATETABLE('Active Users', DATESBETWEEN('Active Users'[TIMESTAMP].[Date], MAX('Active Users'[TIMESTAMP].[Date])-45, MAX('Active Users'[TIMESTAMP].[Date]))),

            'Active Users'[PENDO_VISITOR_ID]),

        "TimestampCount",

            CALCULATE(

                COUNTROWS(

                    SUMMARIZE(

                        CALCULATETABLE('Active Users', DATESBETWEEN('Active Users'[TIMESTAMP].[Date], MAX('Active Users'[TIMESTAMP].[Date])-45, MAX('Active Users'[TIMESTAMP].[Date]))) ,'Active Users'[PENDO_VISITOR_ID]))

                 ,ALLEXCEPT('Active Users','Active Users'[PENDO_VISITOR_ID])))

    ,[TimestampCount] >= 5)

,1)

Has anyone come across this behaviour before? Note this renders perfectly when I enter a sample date range and push the output into a table. If someone can help with point 2 as well, that would be terrific.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach/patern

1. Seperate the time from the time stamp column to extract only the Date column.  Do this in the Query Editor or a calculated column formula in the Data Model
2. Create a Calendar Table
3. Create a relationship (Many to One and Single) from the Date column created in step 1 above to the Date column of the Calendar table
4. Create a slicer and drag Date from the Calendar Table.  Select a certain date in the slicer.
5. Write this measure

log in count = countrows('Active users')

log in count in past 45 days = calculate([log in count],datesbetween('calendar'[date],min('calendar'[date])-44,min('calendar'[date]))

Active users = countrows(filter(values('Active users'[Pendo_Visitor_id]),[log in count in past 45 days]>=5))

Active tenants = countrows(filter(values('Active users'[Tenent_id]),[Active users]>=5)

Hope this helps.

[amedvedev]

This works great, thank you so much!

[Ashish_Mathur]

You are welcome.

[v-zhangti]

Hi, @amedvedev 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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