Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?