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
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.