Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have below Date/time column
Date/Time |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:52:10 PM |
1.1.21 1:55:10 PM |
1.1.21 1:55:10 PM |
1.1.21 1:55:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
1.1.21 2:00:10 PM |
2.1.21 1:00:10 PM |
2.1.21 1:00:10 PM |
2.1.21 1:00:10 PM |
2.1.21 1:00:10 PM |
2.1.21 2:00:10 PM |
2.1.21 3:00:10 PM |
I need to count, how many records are present on each date. Need output as expected
Date/Time | Count |
1.1.21 | 18 |
2.1.21 | 6 |
My data is huge and it's direct query. Can anyone advise how to calculate the count?
Solved! Go to Solution.
That's really odd!
Try creating the calculated column as follows:
DateColumn = INT ( TestTable[DateTimeColumn] )
It will default to a "Whole Number" data type. Set it back to "Date" and then use it in your visual it then all seems to work:
I think it must be something to do with how direct query is folding back to source. Will look into it some more as don't have a good answer at the moment.
I think if you create a calculated column that's directly equal to your existing one.
eg New Column = TableName[Original Column]
you can set it's data type to "date".
At that point COUNTROWS should do the job.
(Not by a computer so think you need the calculated column step because it's direct query but might work without it)
Not sure whether I am doing correct
Please advise
That's really odd!
Try creating the calculated column as follows:
DateColumn = INT ( TestTable[DateTimeColumn] )
It will default to a "Whole Number" data type. Set it back to "Date" and then use it in your visual it then all seems to work:
I think it must be something to do with how direct query is folding back to source. Will look into it some more as don't have a good answer at the moment.
Doing it my original way the conversion to date wasn't being folded back to source (you can see the SQL query if you go to View >> Performance Analyser.
My INT suggestion will work but a better way would be to change the datatype in Power Query:
@bcdobbs my data is big, I am using a direct query. Unable to change in Power Query. Any other method?