Forum Discussion
Unable to group date column for count or matrix
I am using ADO to injest data on the scrum teams backlogs to create a report on work items created and resolved over time. I am trying to create a line chart with date of workitemid's with two lines plotted by month, count of workitems created and resolved. I have a Date table and built relationships between created and resolved dates in the workeitems table to the date table, off of other fabric threads on this topic, but the line chart will not map. In addition, I was trying to perform a matrix where the date shows as the row and the value is count of date created, but it is also remaining flat, showing every work item count not grouped by day.
Date Table: Date
DateTable = CALENDAR(MIN('WorkItems'[CreatedDate]), MAX('WorkItems'[ResolvedDate]))
WorkItems Table:
| Workitemid | Created Date | Resolved Date |
| 111111 | 5/3/25 | 7/2/26 |
| 222222 | 5/3/25 | 7/15/26 |
| 333333 | 5/4/25 | 6/6/26 |
| 444444 | 5/4/25 | 6/6/26 |
| 555555 | 5/4/25 | 6/6/26 |
| 666666 | 5/4/25 | |
| 777777 | 5/5/25 |
Created and resolved are date types, as is the data table. I have put show items with no data. There is data in the tables.
The releationships in the data model are built according to prior solutions in Fabric suggestion.
Active relationship between WorkItems[Created Date] and DateTabe[Date]
Inactive relationship between WorkItems[Resolved Date] and DateTabe[Date]
I would expect the result to show count in either matrix or line graph where created and resolved are this
Created Date
5/3/25 - 2 work items
5/4/25 - 4 work items
5/5/25 - 1 work item
Resolved Date
6/6/26 - 3 work items
7/2/26 - 1 work Item
7/15/26 - 1 work item
Created and Resolved Measure uses this formula:
Count_CreatedDate =
CALCULATE (
COUNTROWS ( WorkItems ),
USERELATIONSHIP ( 'DateTable'[Date], WorkItems[CreatedDate] )
)
When i plot them on the line graph i get no lines. Xaxis Date Table [Date], Y-axis WorkItems [Count_CreatedDate]
When I make a matrix,
Table is flat
| Created Date | Count_CreatedDate |
| 5/3/25 | 1 |
| 5/3/25 | 1 |
| 5/4/25 | 1 |
| 5/4/25 | 1 |
| 5/4/25 | 1 |
| 5/4/25 | 1 |
| 5/5/25 | 1 |
I would have expected
| Created Date | Count_CreatedDate |
| 5/3/25 | 2 |
| 5/4/25 | 4 |
| 5/5/25 | 1 |
Make sure that both created and resolved dates are of date type in both Power Query and in Power BI Desktop. If you set type to date in Power BI Desktop but leave it as DateTime in Power Query then the time part is still retained, although you cannot see it in the table view.
This would explain the non-grouping of apparently equivalent rows in the matrix, and it would also effectively invalidate both relationships to the date table, as only those created / resolved dates with a time part of midnight would correctly relate to the date table.
6 Replies
Hi jesslandmann ,
For this you need to create two relationships between the tables, one active and other inactive and two measures:
The relationshis are between:
- DateTable[Date] - WorkItems[CreatedDate] - Active
- DateTable[Date] - WorkItems[ResolvedDate] - Active
Measures to be created:
Created = COUNTROWS(WorkItems) Resolved = CALCULATE([Created], USERELATIONSHIP(DateTable[Date], WorkItems[ResolvedDate]))Now this will return the result you need when you use the date from the date table on you visuals:
In my original post, I had described already doing this and still fails but thanks for the reply. This is not the solution for my problem as I already had the model built that way.
nice
Make sure that both created and resolved dates are of date type in both Power Query and in Power BI Desktop. If you set type to date in Power BI Desktop but leave it as DateTime in Power Query then the time part is still retained, although you cannot see it in the table view.
This would explain the non-grouping of apparently equivalent rows in the matrix, and it would also effectively invalidate both relationships to the date table, as only those created / resolved dates with a time part of midnight would correctly relate to the date table.
I will try this later today and see if it works. Thanks.
This resolved my issue! Thank you so much!
