cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csalinas
Frequent Visitor

Count and Display Repeated Date Values

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: 

 

2023-01-20_14-16-14.png

 

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:  

 

2023-01-20_14-15-55.png

 

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.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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):
image.png

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:
image.png

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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):
image.png

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:
image.png

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!

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!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.