Forum Discussion
Matrix in PowerBi shows same values for each row
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:
26 Replies
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]))
)
)
)
Ilość serwisów =
Calculate(
COUNTROWS( SAX ),
Keepfilters(
FILTER(
SAX,
(SAX[SVCDATECREATED].[Rok] = 2024 || SAX[SVCDATECREATED].[Rok] = 2025) &&
NOT(ISBLANK(SAX[SERVICE]))
)
)
)
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?
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.
