data collected at different time interval but shown as if at same time interval

[Qianru221]

As shown in picture above. I plotted a line graph and turn it into dots since I dont want the dots to be connected.

My problem now is that when I drill down the data, not every day is shown on the x-axis (which is what I want). It will be still ok if the shown dots are correctly spaced. For example, the data of August 19, 22, 24, 25,26 are equally spaced, which created a false impression that the dots of data are collected at an equal interval of date, which are not.

Thank you in advance.

[arthastic · Accepted solution]

Hi!

You can create a measure like this

calculate(values'datestabel'[date],

     if(isblank(sum('yourtable'[columnwithvalues])),

          blank() , --(or 0 depending on what you want to see)

          sum('table'[column])
        )

)

to show blank (zero) values. 

Regards,

Arthur

[v-huizhn-msft]

Hi @Qianru221,

Please set the X-axis as Categorical type as the screenshot below shown, and check if it works fine.



If this still doesn't resolve your issue, do you mind share your .pbix file for further analysis?

Best Regards,
Angelia

[Qianru221]

Hi Angelia,

Thank you for your information. However the method does not work in this case. I cannot share the data with you either.  

[v-huizhn-msft]

Hi @Qianru221,

In your resource table, is there every day of data? If it doesn't have, please create a Calendar table, and create a rebationship between fact table and Calendar table, then try the solution above.

Best Regards,
Angelia

[Qianru221]

hmm. Indeed I tried to create a calendar table and connect the calendar table date to the date that has values. However it is still not working. The setting has to stay in categorical, because I need the marker topple to be on. I only want the data points to be shown and not the lines in between. If the data is set to continuous, indeed the problem is solved, however the data points cannot be shown any more....So it is really confusing.

[v-huizhn-msft]

Hi @Qianru221,

If we set the x-asix as categorical, the dots shows according to your values in the field shown on x-axis. If there is value on  August 19, it will display on the chart. We can't set same time internal, for example  August 19, 21, 23, 25, 27 and so on.

Best Regards,
Angelia

[Qianru221]

Hi @v-huizhn-msft,

Thank you for your reply.

My point was, it the data are on e.g Aug 1st, Aug 10th and Sep 1st, can the distance between the dots have a horizontal distance that is proportional to the time difference in between? (as shown in my very 1st post)

Thanks.

[v-huizhn-msft]

Hi @Qianru221,

We can't set values on x-axis that proportial to the time difference directly.  You can create a calendar, set x-axis and show all the dates x-axis, the y-value will display proportionally to the time difference, because there is no y value in some dates.

Best Regards,
Angelia

[Qianru221]

@v-huizhn-msft

Yes. That is actually what I have done:

1. I have created a time calendar table with every day ranging from 2015-2017

2. I have a table with dates and numbers.

3. I have connected the dates with numbers to calendar table

4. I set the time in time calendar table as X axis

5. I set the numbers as Y axis.

Still.....

[v-huizhn-msft]

Hi @Qianru221,

For your requirement, the x-axis should show all day of each month, right?

Best Regards,
Angelia

[Qianru221]

Yes. Or at least the points above are distanced proportionally to the time they are collected (should of be equally distanced since they are collected at different time intervals)