Matrix in PowerBi shows same values for each row

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.

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.