Forum Discussion
Helper IMatrix in PowerBi shows same values for each row
Helper Iv-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?
Community SupportHi 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
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
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.- MSiwek
v-ssriganesh now measure is working but it shows nothing even if I'm using new visual just with measure that you gave me 😞