Entry missing from drop down

[muelledg]

Good morning,

I have made a PowerBI report that provides two tables and several slicers. The tables report on time expended on a work activity, by role, for a given time period. The slicers allow the user to select which work activity (e.g. database development) and also the time period (i.e. the time when the work was performed expressed as XX/XX/XXXX date). An odd thing has occurred, when there is no time entry for the time period selected, then the work activity does not appear in the drop down. For example, if no work was done on database development during the time period selected, then database development does not appear in the slicer. 

I need the report to allow the user to select any work activity even when there is no time reported during the time period selected. Can someone offer suggestions on how I can get the work activity to appear in the drop down? 

[DataNinja777 · Accepted solution]

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,

[muelledg]

Thanks @DataNinja777. That definitely helped. Now I am trying to get the table to render. There are two tables. One table contains the role (e.g. DBA) and the hours reported that responds to the time period selected. The other table lists role, total hours expended to date, and percent of hours remaining. I am not able to get the values to populate in the tables. Working with your final DAX expression. I have a separate table for work activities and a separate date table. It is the "TotalHours" element of the expression that is causing me difficulty. Cannot get past that in the expression. Any ideas?

[johnt75]

It sounds like all of your data is coming from a flat table rather than a star schema, which is the type of schema that  Power BI is highly optimised to work with. You can create a work activity dimension table like

Work Activity = DISTINCT ( 'Table'[Work Activity] )

Create a one-to-many relationship from this new table to your existing table and use the column from the new table in your slicer and visuals.

You should also create a proper date table, there are many articles and videos on how to do that. Without a date table any dates which don't appear in your data will not appear as options in the slicer.