Display All Users with Activity Data (Including Zeros)

[powerbiuserrbt]

I'm working on a Power BI report where I need to show activity data for all users, even those who have zero activity within a selected date range.

Here’s what I’m trying to achieve:

Include All Users:

My dataset already includes all users, even those with no activity. Each user should be listed in the report, with an activity count of zero if they didn't have any activity.

Date Table:

I’ve created a Date Table that covers all relevant dates and linked it to the Activity Date in my data.

DAX Measure:

I need help creating a DAX measure that sums up activity counts for all users within the selected date range, including zeros for inactive users.

Here’s the DAX formula I’m trying to use:

Total Activity with Zeros =
SUMX(
ADDCOLUMNS(
ALL('YourUserTable'),
"ActivityCount",
CALCULATE(SUM('YourActivityTable'[Activity Count]),
FILTER(
'YourActivityTable',
'YourActivityTable'[Activity Date] >= MIN('DateTable'[Date]) &&
'YourActivityTable'[Activity Date] <= MAX('DateTable'[Date])
)
)
),
[ActivityCount]
)

----------------------------------------------------------------------------------------------------------------------

And i tried the below one also

Table =

VAR DateRange =

    FILTER(

        DISTINCT('Query1'[ActivityDate]),

        'Query1'[ActivityDate] >= DATE(2024, 8, 20) && 'Query1'[ActivityDate] <= DATE(2024, 8, 21)

    )

VAR AllUsersAndDates =

    CROSSJOIN(

        DISTINCT('Query1'[StaffId]),

        DateRange

    )

VAR EngagementData =

    ADDCOLUMNS(

        AllUsersAndDates,

        "Viewed", COALESCE(FIRSTNONBLANK('Query1'[Viewed], 0), 0),

        "Liked", COALESCE(FIRSTNONBLANK('Query1'[Liked], 0), 0),

        "Commented", COALESCE(FIRSTNONBLANK('Query1'[Commented], 0), 0),

        "Total Engagements", COALESCE(FIRSTNONBLANK('Query1'[Total Engagements], 0), 0)

    )

RETURN

SELECTCOLUMNS(

    EngagementData,

    "StaffId", [StaffId],

    "StaffName", FIRSTNONBLANK('Query1'[StaffName], BLANK()),

    "Viewed", [Viewed],

    "Liked", [Liked],

    "Commented", [Commented],

    "Total Engagements", [Total Engagements]

)

----------------------------------------------------------------------------------------------------------------------

Visual:

I’m using a table or matrix visual in the report, displaying the User field and the Total Activity with Zeros measure.
The visual should show all users, with activity summed for active users and zeros for inactive users.

Expected Outcome:

When filtering by a date range, I want to see all users (e.g., 20 users total):
5 Active Users: Their activity data should be summed and displayed.
15 Inactive Users: Their activity count should show as zero.

Is the DAX measure correct for achieving this? If not, how can I modify it to ensure all users are included, even those with zero activity within the selected date range?

[Rupak_bi · Accepted solution]

Hi, if you just want to see the names have zero count you can simply use below syntax to create a measure.

Count= max(0,calculate(sum(table, activity count))).

This will bring all the names and dates

[Anonymous · Accepted solution]

Hi All
Firstly  Rupak_bi thank you for your solution!
And @powerbiuserrbt , I think the key point of your requirement is to ensure that all users are shown their activity regardless of whether they are active in the selected date range or not, hopefully my answer addresses your question!

Measure 2 = 
VAR SelectedStartDate = MIN('Data Table'[Date])
VAR SelectedEndDate = MAX('Data Table'[Date])
RETURN
MAXX(
   ADDCOLUMNS(
       ALL('Table'[User]), 
       "ActivityCount",
       CALCULATE(
           SUM('Table'[ActivityCount]),
           'Table'[ActivityDate] >= SelectedStartDate &&
           'Table'[ActivityDate] <= SelectedEndDate,
           TREATAS(VALUES('Table'[User]), 'Table'[User]) 
       )
   ),
   COALESCE([ActivityCount], 0) 

If you have any other questions, check out the PBIX file I uploaded and I would be honoured if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[powerbiuserrbt]

Thanks  xingshen

[Rupak_bi · Accepted solution]

Hi, if you just want to see the names have zero count you can simply use below syntax to create a measure.

Count= max(0,calculate(sum(table, activity count))).

This will bring all the names and dates

[powerbiuserrbt]

Thanks Rupak