Forum Discussion

cjemmott's avatar
cjemmott
Icon for Advocate IV rankAdvocate 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
cjemmott's avatar
cjemmott
Icon for Advocate IV rankAdvocate 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Advocate IV rankAdvocate IV
    9 years ago

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

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

      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. :smileyhappy:

       

      Regards