Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.