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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Matthew247
Regular Visitor

It is so simple - just dates on a line graph...

All,

Too many hours wasted trying to get this to work... I have a report that has a Direct Query link to a Dynamics CRM table. The table contains fields that record when cases were created (Case). It uses the default Created On field which is a Date and Time field (Time zone adjustment User local if that is relevant). I want a chart that has dates along the bottom and Count of the Cases field to show how many cases were created each date. Simple...

But the chart only ever shows 1 record per date (with the odd exception) as its taking each time stamp as a unqiue value - see below. I just want it to group it by dates and ignore the time. I have attempted to play with formats but can't get it working. This is an out the box Microsoft Date and Time field so I know the data is good. Help please...!

 

Matthew247_0-1700160117371.png

 

11 REPLIES 11
Matthew247
Regular Visitor

Thanks - can I do that if its a Direct Query link to the data?

yes, you can have calculated columns in Direct Query as long as they originate from the same row.

lbendlin
Super User
Super User

Add a calculated column that gets the DATEVALUE() of your timestamp, and use that in the X axis instead.

For some reason its saying that the column is not suitable for this - but its a standard date time automatic field from Dynamics? 

Matthew247_0-1701347306059.png

 

You need to create a calculated column, not a measure.

Thanks for the reponse and patience. Like this? No joy

Matthew247_0-1701348606714.png

 

The sources for a calculated column in Direct Query mode must come from the same table row. Are you creating that column in the Incidents table?

So you are correct -wrong table. Now it is and accepted the col. But now when I just try and view it as a table it wont show the data? 

Matthew247_0-1701350430543.png

 

what does the error message details link say?

Sure: 

Matthew247_0-1701361513903.png

 

That's not cool. It should have worked. Sorry!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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