Forum Discussion
Count cases by Hour
Hi Anonymous ,
That is no problem, thanks for sharing it like a copyable table 🙂 I loaded the data into Power BI as table Cases. THen I created a calculated table like this:
Table =
ADDCOLUMNS(
GENERATESERIES(1, 24),
"Count",
COUNTROWS(FILTER('Cases', HOUR('Cases'[Time]) = [Value])))The GENERATESERIES creates a column from 1 through 24, then I create a second column "Count" and I count the rows where the Hour of Cases[Time] is the same as the current row of the table we are creating. The result is this:
Does this meet your requirements?
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
I can't create a new column or table, as this data is coming from SQL server. I can only create a new measure.
I don't think this will work as a new measure. I tried it, but it doesn't work.
Thanks for your help though
Hi Anonymous ,
That seems like super important information, would've saved me a lot of time if you had put that in your opening post..
You can't create a measure that returns a 'list' of numbers, and as far as I know you can't use the 'hour' in a date hierarchy (also, no hierarchies are created for you in directquery mode). So, without being able to create a list of numbers you also can't create a measure that is evaluated against every row, right? Is it possible to not use direct query?
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Sorry about not clarifying. I asked for a new measure in the initial post, but I didn't specify it was from SQL.
I'll talk with the SQL person at the company to see if she can throw in an extra column
Sorry about any issues. Thank you again