How to add NA to table visual

[RKK]

Hi,

I'm creating a resource report that uses following tables: one listing each resource name and their manager names, and the other one is assignment table records with assignment_id, date, manager_status, week start date, end date, resource name, hours_worked. One to many relationships between resource and assignment table using resource name.

The report displays a table with the resource name, manager name, week start date, manager_status, and hours_worked. When a week(week start date) is selected in the slicer, the table will show the assignments submitted that week. If a resource hasn't submitted an assignment, the fields for "worked hours" and "manager status" are blank because no data exists in the backend.

My goal is to replace these blanks with "N/A" to indicate missing submissions. Additionally, I want to count these "N/A" entries and display the total in a card.

The check here is there is no data available at the backend to make the changes, because will get entires once resources submitted their assignment sheet.

Can anyone guide me on how to set this up?

[Bipin-Lala · Accepted solution]

Hi @RKK ,

I worked on a similar report setup recently. We had a User table (containing all user data) and a DailyTimesheet table that captured daily timesheet entries. If a user didn’t submit a timesheet for a particular day, no entry existed, similar to your case.

Initially, we used measures to mark missing entries as “Entry Missing,” but this approach complicated calculations and analysis.

Instead, we did the following:

1. Created a MasterTimesheet table by performing a cross join between the Date (last 3 months) and User ID to generate entries for each user for each day.
2. Linked MasterTimesheet and DailyTimesheet tables on Week Date and User ID via a left join (or relationship) to pull timesheet data into MasterTimesheet. Submitted days populated data, while unsubmitted days had nulls (replaced with “N/A”).
3. Linked MasterTimesheet with the User table to include user information for reporting.

This approach provided a complete view of timesheet data, including missing entries, making it easier to analyze. I recommend a similar setup in your case.

Let me know if you need further help! 😊