Forum Discussion
Matrix in PowerBi shows same values for each row
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:- Check if there is any filter affecting this visual.
- Take a table and add the fields-Zmiany[EmployeeID],Zmiany[Nazwa grupy],Zmiany[DateFrom],Zmiany[DateTo] Total Services to validate the data.
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-Power-BI-Forum/ba-p/963216
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.