Hi All,
I have a problem with connections/measure because my simple measure shows same value for each row.
I'm using "Nazwa grupy" from "Zmiany" Date from "Kalendarz" and my measure is in values:
Ilość serwisów = COUNTROWS(
FILTER(
SAX,
(SAX[SVCDATECREATED].[Rok] = 2024 || SAX[SVCDATECREATED].[Rok] = 2025) &&
NOT(ISBLANK(SAX[SERVICE]))
)
)
I can't give you a file cause of the organizational policy but maybe someone would know what happened with my data?
Hi @MSiwek,
Thank you for bringing your query to the Microsoft Fabric Community Forum.
I have reproduced your issue using my sample data and followed the steps below to achieve the expected result. I have also attached a screenshot of the expected output and the .pbix file for your reference.
Steps Taken to Reproduce & Solve the Issue:
- I have Created six tables in Power BI using the Enter Data feature and established relationships between them.
- Created the following measure to correctly count services per department:
Total Services =
VAR SelectedDept = SELECTEDVALUE(Zmiany[Nazwa grupy])
RETURN
CALCULATE(
COUNT(SAX[SERVICE]),
TREATAS(VALUES(Zmiany[EmployeeId]), SaxNames[UserId])
)
Expected Output Screenshot for your reference:
Thank you, @Deku & @andrewsommer for sharing your valuable insights.
If this is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
@v-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.
Hi @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.
@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?
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.
@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.
Hi @MSiwek,
Thank you for the update. The error "A single value for column 'Date' in table 'Calendar' cannot be determined" occurs because the Total Services measure references 'Calendar'[Date] directly without specifying an aggregation (e.g., MIN, MAX) in a context where multiple dates might exist, such as in a matrix visual with multiple rows or columns.
To fix this, we need to wrap 'Calendar'[Date] in an aggregation function like MIN or MAX within the measure. Since you’re comparing dates, MIN('Calendar'[Date]) should work for your scenario. Here’s the updated measure:
Total Services =
CALCULATE(
COUNT(SAX[SERVICE]),
FILTER(
Zmiany,
Zmiany[EmployeeId] IN VALUES(SaxNames[UserId]) &&
MIN('Calendar'[Date]) >= Zmiany[DateFrom] &&
(ISBLANK(Zmiany[DateTo]) || MIN('Calendar'[Date]) <= Zmiany[DateTo])
),
TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED])
)Please update the measure this should resolve the aggregation error.
I hope this works for you. If it does, kindly “Accept it as a solution” and give it a 'Kudos' to help others locate it easily.
Thank you.
@v-ssriganesh now measure is working but it shows nothing even if I'm using new visual just with measure that you gave me 😞
Hi @MSiwek,
Thank you for the update. I’m glad the measure is now working without errors, but not showing any values in your visual, even with a new visual. This likely means the measure is returning no results due to the filtering logic or data mismatches.
The issue might be due to the date ranges in Zmiany[DateFrom] and Zmiany[DateTo] not overlapping with the dates in SAX[SVCDATECREATED]. The relationships between tables not filtering data as expected.
To resolve this:
- Check the dates in SAX[SVCDATECREATED] fall within the ranges defined in Zmiany[DateFrom] and Zmiany[DateTo]. For example, if Zmiany[DateFrom] is 01.01.2025 and Zmiany[DateTo] is 31.03.2025, but your SAX[SVCDATECREATED] dates are outside this range (e.g: in 2024), the measure will return no results.
- Confirm that the relationships are set up correctly:
- SAX[SVCCREATEDBY] → SaxNames[Saxname]
- SaxNames[UserId] → Zmiany[EmployeeId]
- SAX[SVCDATECREATED] → Calendar[Date]
If the issue persists, please check the date ranges in your SAX and Zmiany tables and confirm that there are matching records.
If this helps then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Hope this works for you!
Thanks.
Hi @v-ssriganesh,
I have checked everything that you mentioned above. I made a correction in Zmiany tables in DateFrom (it was 1900-01-01) now I made 2024-01-01 and DateTo is = TODAY or real date of DateTo if exist.
About connections:
- SAX[SVCCREATEDBY] → SaxNames[Saxname] (Many to one)
- SaxNames[UserId] → Employees[EmployeeId] (One to one)
- Employees[EmployeeId] → Zmiany[EmployeeId] (One to many)
- Employees[EmployeeId] → PresenceStates[EmployeeId] (One to many)
- SAX[SVCDATECREATED] → Calendar[Date] (Many to one
Now measure is showing values but only few 😞
Hi @MSiwek,
Thank you for the update and for checking the details I mentioned. I’m glad the measure is now showing some values after you corrected the Zmiany table.
Here are the few steps you can consider:
- Check the range of dates in SAX[SVCDATECREATED] to ensure they fall within the updated Zmiany date ranges (from 2024-01-01 to TODAY() or the real DateTo). You can add SAX[SVCDATECREATED] to a table visual to see the date distribution.
- If some SAX[SVCDATECREATED] dates are outside this range, you may need to adjust the Zmiany date ranges or modify the measure to include those dates.
- Ensure all SAX[SVCCREATEDBY] values have corresponding matches in SaxNames[Saxname]. You can test this by creating a table visual with SAX[SVCCREATEDBY] and SaxNames[Saxname] to identify any unmatched records.
- Similarly, confirm that all SaxNames[UserId] values exist in Employees[EmployeeId], and all Employees[EmployeeId] values exist in Zmiany[EmployeeId].
- Add a table visual with Zmiany[EmployeeId], Zmiany[Nazwa grupy], Zmiany[DateFrom], Zmiany[DateTo], and the Total Services measure. This will help you see which employees and departments are returning values and which are not. If some employees are missing, it might indicate a data mismatch in the relationships.
If the date filtering is too restrictive, you can temporarily remove the date conditions in the measure to see if more values appear.
If this helps, please “Accept it as a solution” and give a “Kudos” to make it easier for others to find.
Hope this works for you!
Thank you.
Hi @v-ssriganesh,
1. Dates in SAX[SVCDATECREATED] and Zmiany[DateTo] are in same ranges.
2. Dates are ok.
3. All matches are ok
4. I have a few EmployeeId from Employess which doesn't have corresponding matches in SaxNames so I think there is a space where we can improve.
5. In that visual I can see only record with values and there are only few employees.
Hi @MSiwek,
Thank you for the detailed update.
The issue stems from the measure’s logic: Zmiany[EmployeeId] IN VALUES(SaxNames[UserId]) ensures that only EmployeeId values present in SaxNames[UserId] are included. Since some EmployeeId values in Employees (and thus in Zmiany) don’t exist in SaxNames, those employees are excluded from the results.
Try below measure:
Total Services =
CALCULATE(
COUNT(SAX[SERVICE]),
FILTER(
Zmiany,
NOT ISBLANK(
CALCULATE(
COUNTROWS(SaxNames),
SaxNames[UserId] = Zmiany[EmployeeId]
)
) &&
MIN('Calendar'[Date]) >= Zmiany[DateFrom] &&
(ISBLANK(Zmiany[DateTo]) || MIN('Calendar'[Date]) <= Zmiany[DateTo])
),
TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED])
)Please update the measure with the version above and enable "Show items with no data" in your visual. This should display all employees, with a count of 0 for those without matches in SaxNames.
Since some EmployeeId values in Employees don’t have matches in SaxNames, confirm if this is expected. If these employees should have corresponding SaxNames entries, you may need to update your data source to include the missing SaxNames records.
I hope this works for you. If it does, please “Accept it as a solution” and give it a 'Kudos' to help others find it easily.
Thank you.
Hi @v-ssriganesh, when I'm trying to use this measure I have a error: The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.
Yes I can cofirm that not all employees from Employees table should have corresponding matches in SaxNames.
Hi @MSiwek,
Thank you for the update.
The error occurs because the FILTER function in the Total Services measure references columns from multiple tables (Zmiany, SaxNames, Calendar), which isn’t allowed in a True/False expression. Let’s fix this by using variables to handle the multi-table logic.
Here’s the corrected measure:
Total Services =
VAR MinCalendarDate = MIN('Calendar'[Date])
VAR MatchingSaxNames =
CALCULATETABLE(
VALUES(SaxNames[UserId]),
SaxNames
)
RETURN
CALCULATE(
COUNT(SAX[SERVICE]),
FILTER(
Zmiany,
Zmiany[EmployeeId] IN MatchingSaxNames &&
MinCalendarDate >= Zmiany[DateFrom] &&
(ISBLANK(Zmiany[DateTo]) || MinCalendarDate <= Zmiany[DateTo])
),
TREATAS(VALUES('Calendar'[Date]), SAX[SVCDATECREATED])
)Please update the measure and test it in your visual. Ensure "Show items with no data" is enabled to display all employees.
If this helps, kindly consider marking this response as "Accept as Solution" and giving it a "Kudos" to assist other community members facing similar challenges.
Thanks.
Hi @v-ssriganesh, now measure doesn't have errors but shows values like in previous measures but it's only values matches with few groups->names and surnames. It should be about 500k total services but measure shows stil 441 total services and all of them are in 2024.01.
There is no filter flow from your Zimiany table to your SAX table so you will not get a proper filter contect if you use the "Nazwa grupy" from "Zmiany" and the measure built off of SAX.
Without knowing your datamodel it is difficult to give you the best path forward. With that said I would default to Roche’s maxim and say you probably should look at doing some upstream work.
Please mark this post as solution if it helps you. Appreciate Kudos.
I can make a direct connection between "Zmiany" and "SAX" because I can improve my sql query or merge data in PQ to have also column SaxName in "Zmiany" table but what about Employees and Presence States table? If I made direct connection between "Zmiany" and "SAX" "SaxNames" wouldn't be used and could be delated.
@andrewsommer i made a sample of my data.
SAX:
| SVCDATECREATED | SERVICE | SVCCREATEDBY |
| 25.03.2025 | NAME OF SERVICE 1 | SWK |
| 01.03.2025 | NAME OF SERVICE 2 | KWS |
| 14.03.2025 | NAME OF SERVICE 1 | SWK |
Połączenie:
| SVCCREATEDBY |
| SWK |
| KWS |
SaxNames:
| Saxname | UserId |
| SWK | 1 |
| KWS | 2 |
Zmiany:
| EmployeeId | Nazwa grupy |
| 1 | Dep 1 |
| 2 | Dep 2 |
Employees:
| EmployeeId | NAME AND SURNAME |
| 1 | MICHAEL TEST |
| 2 | JACOB TEST |
PresenceStates
| EmployeeId | WorkinHours | Date |
| 1 | 8 | 01.01.2025 |
| 2 | 8 | 02.01.2025 |
Would this be helpful?
Added keepfilters to intersect the current a calculated filter contexts.
Ilość serwisów = COUNTROWS(
Keepfilters(
FILTER(
SAX,
(SAX[SVCDATECREATED].[Rok] = 2024 || SAX[SVCDATECREATED].[Rok] = 2025) &&
NOT(ISBLANK(SAX[SERVICE]))
)
)
)
