Forum Discussion
Helper IMatrix in PowerBi shows same values for each row
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.
Helper Iv-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 😞
- v-ssriganesh
Hi MSiwek,
Thank you for the update. I’m glad the measure is now working without errors, but not showing any values in your visual, even with a new visual. This likely means the measure is returning no results due to the filtering logic or data mismatches.
The issue might be due to the date ranges in Zmiany[DateFrom] and Zmiany[DateTo] not overlapping with the dates in SAX[SVCDATECREATED]. The relationships between tables not filtering data as expected.
To resolve this:
- Check the dates in SAX[SVCDATECREATED] fall within the ranges defined in Zmiany[DateFrom] and Zmiany[DateTo]. For example, if Zmiany[DateFrom] is 01.01.2025 and Zmiany[DateTo] is 31.03.2025, but your SAX[SVCDATECREATED] dates are outside this range (e.g: in 2024), the measure will return no results.
- Confirm that the relationships are set up correctly:
- SAX[SVCCREATEDBY] → SaxNames[Saxname]
- SaxNames[UserId] → Zmiany[EmployeeId]
- SAX[SVCDATECREATED] → Calendar[Date]
If the issue persists, please check the date ranges in your SAX and Zmiany tables and confirm that there are matching records.
If this helps then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Hope this works for you!
Thanks. - Check the dates in SAX[SVCDATECREATED] fall within the ranges defined in Zmiany[DateFrom] and Zmiany[DateTo]. For example, if Zmiany[DateFrom] is 01.01.2025 and Zmiany[DateTo] is 31.03.2025, but your SAX[SVCDATECREATED] dates are outside this range (e.g: in 2024), the measure will return no results.