Forum Discussion
Helper IMatrix in PowerBi shows same values for each row
Helper Iv-ssriganesh it's almost done. I made mistake in describing table "Zmiany" beacuse it should look like this
| EmployeeId | Nazwa grupy | Date From | Date to |
| 1 | Dep 1 | 01.01.2025 | 31.03.2025 |
| 2 | Dep 2 | 01.01.2025 | |
| 1 | Dep 3 | 01.04.2025 |
So that's mean that one Employee can work in one department and could get promotion to another department and now I have problem with thing when I'm using Nazwa grupy from "Zmiany" in rows, SVCDATECREATED from "SAX" in columns and measure Total Services in values I have EmployeeId in two deparments but measure show the same value for each month and each deparment. So my matrix in powerbi looks like
| Dep 1 | January | February | March |
| EmloyeeId 1 | 100 | 200 | 300 |
| Dep 2 | |||
| EmloyeeId 1 | 100 | 200 | 300 |
And should look like this:
| Dep 1 | January | February | March | April |
| EmloyeeId 1 | 100 | 200 | 300 | 0 |
| Dep 2 | ||||
| EmloyeeId 1 | 100 | 200 | 300 | 200 |
And I think the measure should also have something with dates.
Community SupportHi MSiwek,
Thank you for providing the updated details about the Zmiany table and the expected output. I understand the issue now: the measure wasn’t accounting for the time-dependent department assignments in the Zmiany table (using Date From and Date to). This caused the same values to appear for each department for EmployeeId 1, even though the employee moved from Dep 1 to Dep 3 on 01.04.2025. I’ve updated the solution below to fix this by modifying the Total Services measure to filter services based on the date range during which an employee was in a department.
- I’ve updated the Zmiany table in my sample data to match your corrected structure.
- You mentioned using "Date from ‘Kalendarz’," so I assume you have a Calendar table. If not, create one to manage dates consistently:
Calendar = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))- The previous measure didn’t account for the date range in the Zmiany table, which caused the same values to appear for each department. Here’s the updated measure:
Total Services =
CALCULATE(
COUNT(SAX[SERVICE]),
FILTER(
Zmiany,
Zmiany[EmployeeId] IN VALUES(SaxNames[UserId]) &&
'Calendar'[Date] >= Zmiany[Date From] &&
(Zmiany[Date to] IS BLANK || 'Calendar'[Date] <= Zmiany[Date to])
),
TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED])
)- This measure ensures that services are only counted for the department an employee was in during the date range specified in Zmiany[Date From] and Zmiany[Date to].
- The TREATAS function ensures the date filter from the Calendar table (used in the matrix columns) is applied to SAX[SVCDATECREATED].
If you find this information useful, please “Accept it as a solution” and give it a 'Kudos' to assist others in locating it easily.
Thank you.
- MSiwek
v-ssriganesh I made a calendar table using
Calendar = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))
But the measure that you sent shows me this error:
The syntax for 'IS' is incorrect. (DAX(CALCULATE( COUNT(SAX[SERVICE]), FILTER( Zmiany, Zmiany[EmployeeId] IN VALUES(SaxNames[UserId]) && 'Calendar'[Date] >= Zmiany[DateFrom] && (Zmiany[DateTo] IS BLANK || 'Calendar'[Date] <= Zmiany[DateTo]) ), TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED])))).
Also PowerBi shows me red underline on 'Calendar'[Date] I don't know why becuase in my opinion calendar is ok. Could you help me one more time please?
- v-ssriganesh
Hi MSiwek,
Thank you for providing the details. I understand that you’ve created the Calendar table using the DAX expression, but you’re encountering two issues with the Total Services measure:Here is the corrected measure:
Total Services = CALCULATE( COUNT(SAX[SERVICE]), FILTER( Zmiany, Zmiany[EmployeeId] IN VALUES(SaxNames[UserId]) && 'Calendar'[Date] >= Zmiany[DateFrom] && (ISBLANK(Zmiany[DateTo]) || 'Calendar'[Date] <= Zmiany[DateTo]) ), TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED]) )If the issue persists, please confirm the exact name of the Calendar table in the fields pane.
I trust this information proves useful. If it does, kindly “Accept it as a solution” and give it a 'Kudos' to help others locate it easily.
Thank you.- MSiwek
v-ssriganesh now it's better because now I have only problem with aggregation. A single value for column 'Date' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.