Cumulative Unique Count Slicing

[cjemmott]

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 show just the last 18 months. I was able to build this plot, but the way I did it means that it doesn't interact with slicers the way I want it to. Is there a better way to make this?

What I did:

Monthly active user count (gray) is straightforward - I have a table of user activity and I do a unique count of user ID by month. Works exactly like I want.

For cumulative user accounts (orange), I created a query on a different table that includes user creation date:

User Accounts (monthly) =
  CALCULATE (
    COUNT ( 'users'[userId] ),
    FILTER (
      ALL ( 'users'[createdAtMonth] ),
      'users'[createdAtMonth] <= MAX ( userActivity[Month] )
  )
)

I can then filter to the plot by user activity date (last 18 months) and get what I show above.

The problem is that the cumulative user accounts does not react to slicers based on user activity - for example, slicing users marked "premium" in user activity will correctly filter monthly active users (gray), but will not filter cumulative user accounts (orange). Of course this is true, because the tables aren't joined!

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.

Is there a way I can have the cumulative unique count slice based on user interactions, but not the time filter for the display?  Or another way to build this that will behave like I want?

[v-ljerr-msft]

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. 

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]

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]

Hi @cjemmott,

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

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]

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

[v-ljerr-msft]

Hi @cjemmott,

Based on my tests, both the formulas below should work.

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 ()
    )

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 ()
    )

Could you post your some sample/mock data which can be used to reproduce the issue, so that we can help further investigate on it? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards