Count and Display Repeated Date Values

[csalinas]

Hi! I am trying to figure out how to display a longitudinal graph that displays a count (how many people registered per day) over time. I can do it with "import"ed data by dragging the same dataset into the X and Y axes, where the Y axis is defined as "Count of Column 1" - as shown here: 

That's easy enough, but the problem is that I have to use DirectQuery because it is being fed via SQL from the reporting service (Reporting 2.0 in the Cornerstone Learning Management System). Converting or transforming the data in any way I can think of keeps giving me the old "you have to convert the data to import" dialog. At this point, what I'm getting is simply returning a "1" - which kind of makes sense, but is not what I need - as shown here:  

Is there a column, measure, DAX expression or something else I can use to make this data correlation? Any help is appreciated, I am a newbie who knows just enough to be dangerous.

[TomMartens · Accepted solution]

Hey @csalinas ,

for now, I have no idea of what's going on your site, as I can create a line chart that puts a column of type date/time (date works the same) into the X-axis bucket and the same column to the Y-axis bucket (using count as the aggregation function). As you can see from the image below, I'm getting data from a data source connected in direct query mode (a SQL Server):

Nevertheless, the DAX below creates a measure that counts the days, it returns the no of occurrences and returns the same line chart:

count of occurences = 
COUNT(
    'FactOnlineSales'[DateKey]
)

And the picture:

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom

[csalinas]

Hi, I wanted to follow up on the resulution here, which was a little different than expected. I was able to convince the database cat in our organization to let me use Import data instead of DirectQuery. After doing that, it worked you decsribed. I guess sometimes a different angle can be the way to go. Thanks again!

[csalinas]

Hi Tom. Thank you for responding. I believe at least part of my problem is that the "count" option is not avalable in the drop down menu on either the X or Y axis. That's what seems to make the data work on the document with the data Import (as opposed to the DirectQuery). Creating a new measure with the count function you shared is giving me a grand total of everything. And the documentation for the COUNT Dax function is saying, "a

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules."

The problem maybe lies somewhere in one of those things, I'm just not sure which. Thank you so much, I really appreciate the insight, it's helping me think through the problem!