Reoccurring Distinct Counts

[Mactoff]

Hello,

I have a table that logs user activity across my dashboard that looks like this:

My stakeholder has requested to get a count of distinct users who have viewed the report in this quarter and the previous quarter. I am able to show this easily in a table by adding flags for if they viewed in this quarter and the previous quarter for the quarter that is selected, then writing a measure to check if they submitted in both. Example below:

However, the problem is I need to show this visual in a Card, not a table. In the example above my card would show the number 3, because there are only 3 users who have viewed the report in Q1 2023 and Q4 2022.

I tried doing a cumulative count for users <= the selected quarter, however this returns all distinct users who viewed in either quarter.

I also tried building a custom table with Summarize but there is no DistinctCountX function which throws a wrench in that plan.

DAX Samples:

RepeatUsers =
COUNTX(
FILTER (
SUMMARIZE (
'Report views',
'Report views'[User],
'Report views'[Quarter],
"Views", COUNT ( 'Report views'[UserID] )
),
[Views] > 0 && 'Report views'[Quarter] = SELECTEDVALUE(QuarterDim[Quarter])
),
'Report views'[User]
)

RepeatUsers =
VAR SelectedQuarter =
SELECTEDVALUE ( QuarterDim[Quarter] )
Var SelectedPriorQuarter=
SELECTEDVALUE(QuarterDim[PriorQuarter])
VAR Result =
CALCULATE (
DISTINCTCOUNT ( 'Report views'[UserId] ),
FILTER ( 'Report views', ('Report views'[Quarter] <= SelectedQuarter && 'Report views'[YearTerm] >= SelectedPriorQuarter )
)
RETURN
Result

Any help is greatly appreciated!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create it based on what I have checked in the question above.

[Mactoff]

Sorry for all the comments lol, I figured it out. It's because my Quarter filter is using the dimTable Quarter, not the 'Report views' table Quarter I just needed to create a relationship between the two tables. Thanks again!

[Mactoff]

Yea I'm not sure. I generated the extra rows and I still just get "blank" in my card. The measure works fine when I make a table visual and implement User as a row.

[Mactoff]

Yea I believe this isn't working for me because my source table doesn't actually have a recorded row for a Quarter in which they did not view the report, it only has records for when they did view the report. So essentially my sample table above would only have the rows where ViewedReport = 1. I think if I can generate rows for Quarters they did not view this solution should work.

[Mactoff]

I am reviewing now. I was unable to make it work on my first pass but that doesn't mean it doesn't work. Appreciate you putting this together. I'll get back to you later this afternoon.