Forum Discussion

cjemmott's avatar
cjemmott
Advocate IV
9 years ago

Cumulative Unique Count Slicing

Here is the plot I am trying to make:     It shows the cumulative unique count of user accounts (orange) and the count of users that took an action in each month (gray). It is limited to sho...
Need Help
Tutorial Requests
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi cjemmott,

Unfortunately, if I do join the tables then when I filter the plot to show the last 18 months (visual level filter on user activity), the cumulative user account count (orange line) gets messed up - it filters out everything outside of that time.

Instead of using visual level filter on user activity to filter the plot to show the last 18 months, you should also be able to do it within your measures. Without the visual level filter, you can then join the tables to filter cumulative user accounts in this scenario.

 

Steps below are for your reference. :smileyhappy:

 

1. Use the formula below to add a calculate column called "YearMonth" in your 'userActivity' table.

YearMonth = userActivity[Year] * 12 + userActivity[Month]

2. Use the formulas below to calculate [Monthly active user count] and [cumulative user accounts].

Count of Active User =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        DISTINCTCOUNT ( userActivity[User] ),
        BLANK ()
    )

User Accounts (monthly) =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        CALCULATE (
            COUNT ( 'users'[userId] ),
            FILTER (
                ALL ( 'users'[createdAtMonth] ),
                'users'[createdAtMonth] <= MAX ( userActivity[Month] )
            )
        ),
        BLANK ()
    )

 

Regards

  • cjemmott's avatar
    cjemmott
    Advocate IV
    9 years ago

    Wow, thank you for the help!  What you described does filter the date correctly, but doesn’t fix the underlying problem.  I think I might have been unclear - let me try again:

     

    The remaining issue is that not all accounts are in the userActivity table, and so when I create a relationship between that and users and then plot using the activity month, I end up with a subset for accounts.  Basically, I get the blue line rather than the orange line below:

     

     

    I get the same line with the account line with your way of calculating and mine when I build from users.  Deleting the relationship allows me to get the correct result, but then the other slicers and interactive filters don’t work.

     

    Did that make sense?  Thanks again!

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi cjemmott,

       

      Could you try the formula below to see if it works in your scenario? :smileyhappy:

      User Accounts (monthly) =
VAR currentMonth =
    MAX ( userActivity[YearMonth] )
VAR latestMonth =
    CALCULATE ( MAX ( userActivity[YearMonth] ), ALL ( userActivity ) )
RETURN
    IF (
        currentMonth
            > latestMonth - 18,
        CALCULATE (
            COUNT ( 'users'[userId] ),
            FILTER (
                ALL ( 'users' ),
                'users'[createdAtMonth] <= MAX ( userActivity[Month] )
            )
        )
            - CALCULATE (
                DISTINCTCOUNT ( userActivity[User] ),
                FILTER (
                    ALL ( userActivity ),
                    userActivity[Month] = MAX ( userActivity[Month] )
                )
            )
            + DISTINCTCOUNT ( userActivity[User] ),
        BLANK ()
    )

       

      Regards

      • cjemmott's avatar
        cjemmott
        Advocate IV
        9 years ago

        Unfortunately that gives the same result (blue line again).