Entry missing from drop down

Hi muelledg ,

You're experiencing an issue in Power BI where the "Work Activity" slicer only shows values that have corresponding entries in the selected time period. This happens because the slicer is likely based on your fact table, which only contains entries where time was actually recorded. As a result, if no time was logged for a particular work activity during the selected period, that activity won't appear in the slicer.

To fix this, you need to create a separate dimension table that contains a complete list of all possible work activities. You can do this using a DAX formula like:

WorkActivities = DISTINCT('TimeEntryFactTable'[Work Activity])

Alternatively, if you already have a source table that lists all the valid work activities, use that instead. After creating the WorkActivities table, go to the model view and create a relationship between WorkActivities[Work Activity] and TimeEntryFactTable[Work Activity]. Make sure it's a single-directional relationship from the WorkActivities table to the fact table.

Once the relationship is established, update your slicer to use WorkActivities[Work Activity] instead of the field from the fact table. This will ensure that all work activities are listed in the slicer, even if no time was logged for them during the selected period.

If you'd like your table visuals to continue showing all combinations of work activities and dates, even where there is no data, you can build a table using a DAX formula like:

FinalTable =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'WorkActivities'[Work Activity] ), VALUES ( 'Calendar'[Date] ) ),
    "TotalHours", CALCULATE ( SUM ( 'TimeEntryFactTable'[Hours] ) )
)

This allows the visuals to show blanks or zeros for combinations without any recorded time. Let me know if you'd like help setting this up in your actual data model.

Best regards,