Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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:
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.
Calendar = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))
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])
)
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:
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:
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:
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.
Hello @MSiwek,
Sorry for the delayed reply. Please consider the below steps:
If the issue persists, can you please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information or anything unrelated to the issue or question. Also, show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/How-to-provide-sample-data-in-the-...
Thank you, please continue using Microsoft Fabric community forum.
Hello @MSiwek,
I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.
Thank you.
Hello @MSiwek,
Just checking in to see if you had a chance to review my earlier message and share the requested details. Once we have that, we’ll be better equipped to help you further. Thanks.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
38 | |
30 | |
26 |
User | Count |
---|---|
99 | |
87 | |
45 | |
43 | |
37 |