Forum Discussion
CountRows with UseRelationship is not working
I have a table with Schedule of Tasks. Each task has two dates start and finish.
The main goal is to count the number of tasks by date while maintaining relationship with the tasks to keep crossfiltering
I created two relationships between Calendar table and my Tasks Table
and then I created measures to calculate row counts
cnt_finishes =
CALCULATE(
COUNTROWS(TASKS),
USERELATIONSHIP('Calendar'[Date], TASKS[Finish])
)
This did not work and I am getting weired results. I am wondering what am I doing wrong
I have uploaded a sample pbix file and excel file containing the data to this folder Count UseRL
I actually found the solution which was pretty silly.
In the query editor the dates columns have to be of type Date and Not date/time
So the solution was as easy as changing the type of both start and finish columns to Date.
4 Replies
A quick fix would be to scrap the measures and the calendar table and use a matrix with only the starts and finish dates. Like this:
This gives the matrix visual:
If you found this helpfull please give me thumbs up and if ths solved your problem please mark this as the solution.
Thanks for trying to help but I need to have them linked to calendar table as I have many measures to be combined in a single chart.
I actually found the solution which was pretty silly.
In the query editor the dates columns have to be of type Date and Not date/time
So the solution was as easy as changing the type of both start and finish columns to Date.
It's always the obvious solutions that are the hardest. Good job tho, will def come in handy to know!
