Forum Discussion
Advocate ISlicer and Table Missing Values Despite Matrix Showing All – Calendar Relationship Issue in Power BI
Problem Description
I have a Power BI report where:
A matrix shows resources in the rows along with a measure in the values.
A slicer is built on the same Resource field.
Certain resource names (for example, “Resource A” and “Resource B”) appear in the matrix but are missing from the slicer’s list of values.
In my report, when I display TaskWeeks_Gantt[Resource] in a simple table or slicer, only 7 unique values are shown. However, in a matrix visual that uses my measure, all 9 resources appear. This inconsistency indicates that the filter context—likely influenced by the relationship with the Calendar table—is causing some resource values to be omitted in certain visuals, even though they exist in the data.
- When I break the relationship to the calendar table, all resources show.
There are no hidden filters, the slicer’s “Show items with no data” option is enabled, and the data refresh is current.
(As we can see, the matrix shows 9 people, and the slicer only shows 7...)
(But if I put the Resource field into the filter pane, I see all 9.)
Below are the key table definitions and transformations in my model.
Table Definitions
1. Resource Deliverables Gantt
UNION (
ADDCOLUMNS (
FILTER (
'Client Deliverable Tracker',
NOT ( ISBLANK ( [FieldValuesAsText.Preparer] ) )
&& [FieldValuesAsText.Preparer] <> ""
),
"Role_Tag", "Preparer",
"Role_Type", "Prep 1",
"Resource", 'Client Deliverable Tracker'[FieldValuesAsText.Preparer]
),
ADDCOLUMNS (
FILTER (
'Client Deliverable Tracker',
NOT ( ISBLANK ( [FieldValuesAsText.Preparer] ) )
&& [FieldValuesAsText.Preparer] <> ""
),
"Role_Tag", "Preparer",
"Role_Type", "Prep 2",
"Resource", 'Client Deliverable Tracker'[FieldValuesAsText.Preparer]
),
ADDCOLUMNS (
FILTER (
'Client Deliverable Tracker',
NOT ( ISBLANK ( [FieldValuesAsText.Reviewer] ) )
&& [FieldValuesAsText.Reviewer] <> ""
),
"Role_Tag", "Reviewer",
"Role_Type", "Rev 1",
"Resource", 'Client Deliverable Tracker'[FieldValuesAsText.Reviewer]
),
ADDCOLUMNS (
FILTER (
'Client Deliverable Tracker',
NOT ( ISBLANK ( [FieldValuesAsText.Reviewer] ) )
&& [FieldValuesAsText.Reviewer] <> ""
),
"Role_Tag", "Reviewer",
"Role_Type", "Rev 2",
"Resource", 'Client Deliverable Tracker'[FieldValuesAsText.Reviewer]
)
)
2. TaskWeeks_Gantt
GENERATE (
'Resource Deliverables Gantt',
VAR StartWeek = 'Resource Deliverables Gantt'[Start_Date_BOWeek]
VAR EndWeek = 'Resource Deliverables Gantt'[Due_Date_EOWeek]
VAR NumWeeks =
DATEDIFF ( StartWeek, EndWeek, WEEK )
RETURN
ADDCOLUMNS (
GENERATESERIES ( 0, NumWeeks - 1 ),
"WeekDate",
StartWeek + ( [Value] * 7 ),
"PerWeekHours", DIVIDE ( 'Resource Deliverables Gantt'[Role_adj_TotalHours_dep], NumWeeks ),
"Resource", 'Resource Deliverables Gantt'[Resource]
)
)
3. Calendar table definition
4. Measure (Parent and child) for Value field of Matrix (One does the values, and the other references it and corrects the row and column context for the totals in the matrix visual)
VAR CurrentWeekDate = INT( SELECTEDVALUE('Calendar'[Start of Week Date]) )
VAR MinWeekDate = CALCULATE( MINX( TaskWeeks_Gantt, INT(TaskWeeks_Gantt[WeekDate]) -- or INT(TaskWeeks_Gantt[Start_Date_BOWeek]) if that's the real start ), ALLEXCEPT(TaskWeeks_Gantt, TaskWeeks_Gantt[Resource], TaskWeeks_Gantt[ProjectID_2]) )
VAR MaxWeekDate = CALCULATE( MAXX( TaskWeeks_Gantt, INT(TaskWeeks_Gantt[Due_Date_EOWeek]) ), ALLEXCEPT(TaskWeeks_Gantt, TaskWeeks_Gantt[Resource], TaskWeeks_Gantt[ProjectID_2]) )
RETURN IF( CurrentWeekDate >= MinWeekDate && CurrentWeekDate <= MaxWeekDate, 1, 0 )
VAR vtable = ADDCOLUMNS( CROSSJOIN( VALUES(TaskWeeks_Gantt[Resource]), VALUES(TaskWeeks_Gantt[ProjectID]), VALUES('Calendar'[Start of Week Date]) ), "@Hours", [IsTotalHours_] )
RETURN IF( HASONEVALUE(TaskWeeks_Gantt[Resource]) && HASONEVALUE(TaskWeeks_Gantt[ProjectID]) && HASONEVALUE('Calendar'[Start of Week Date]), // Row-level cell: all dimensions have exactly one value, so just use the original measure. [IsTotalHours_], // Otherwise, re-calculate by summing the row-level measure over all combinations in the visual.
CALCULATE( SUMX(vtable, [@Hours]), VALUES(TaskWeeks_Gantt[Resource]), VALUES(TaskWeeks_Gantt[ProjectID]), VALUES('Calendar'[Start of Week Date]) ) )
Table Relationships of Concern
I have a relationship from TaskWeeks_Gantt[WeekDate] to Calendar[Start of Week Date] (single direction).
My matrix is built on TaskWeeks_Gantt[Resource] in the rows and uses a measure for values.
My slicer is built on TaskWeeks_Gantt[Resource].
“Resource A” and “Resource B” appear in the matrix but not in the slicer.
When I remove or deactivate the relationship to the Calendar table, all resources appear.
I have verified that the WeekDate values for these tasks match dates in the Calendar table (no date/time mismatches).
- To reitierate - If I put the TaskWeeks_Gantt[Resource] field into a simple table, as opposed to the matrix, it also only shows 7 values.
- To continue, if I put the TaskWeeks_Gantt[Resource] field into a matrix with on measure for the values, I only see 7 resources - If I put my measure, then all 9 resources are visualized.
What I’ve Tried
Confirmed there are no hidden filters or slicer interactions.
Checked for trailing spaces, case differences, or other data inconsistencies.
Ensured the Resource column is explicitly included in TaskWeeks_Gantt.
Enabled “Show items with no data” in the slicer.
Verified that the Calendar table covers the correct date range for all tasks.
Very strange behavior!
Despite these steps, the resources still appear in the matrix, and the filter pane, but not in the slicer. Forgive the messy data modelling, I am in development and plan to clean things up.
Is the realtionship to the calendar table even necessary? Any insights or suggestions to help resolve this issue would be greatly appreciated!
Edit the interactions so the other slicers do not affect that slicer. Measures can make fields show in tables that would otherwise not be visual in the context. If you remove the measures from the visual you will likely see on 7 rather than 9
5 Replies
- Deku
Super User
You've shown filter on page, but is there a filter on the visual, or different edit interactions that means a visual is filtering the slicer not the matrix. If you have on the slicer, in the header you'll see 3 horizontal bars, if you click on you'll see if any filters are being applied
- Deku
Edit the interactions so the other slicers do not affect that slicer. Measures can make fields show in tables that would otherwise not be visual in the context. If you remove the measures from the visual you will likely see on 7 rather than 9
- rruthSL
Yes, if I remove the measures, I am back down to 7 resources (Which is not what I would expect, I expect all 9 resrouces in the dataset to be visualized). I've removed interactions and the same behavior persists.
