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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors