Forum Discussion

MSiwek's avatar
MSiwek
Icon for Helper I rankHelper I
1 year ago

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: ...
Need Help
v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

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.

MSiwek's avatar
MSiwek
Icon for Helper I rankHelper I
1 year ago

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?

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

    • MSiwek's avatar
      MSiwek
      Icon for Helper I rankHelper I
      1 year ago

      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.

      • v-ssriganesh's avatar
        v-ssriganesh
        Icon for Community Support rankCommunity Support
        1 year ago

        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.