Trend axis trouble

[Anonymous]

Hi all,

I am trying to create a kpi which shows how many active documents are in the system at any given hour on a particular day (i.e. from CreationDate until CompletionDate). I am currently working with historical data so it's from CreationDate until CompletionDate where either CompletionDate is NULL or Completion Date > SelectedDate (12Feb2018). I have set up a measure to calculate the active documents and a table of 24 hours from 00:00-23:00. This is my code:

ActiveDocs = 
CALCULATE(
    COUNTA('Cases'[CreationDate]), 
    FILTER(
        ALL('Cases'), 
        'Cases'[CreationDate] < DATE(2018, 02, 12)
    ), 
    FILTER(
        ALL('Cases'), 
        OR(
            ISBLANK('Cases'[CompletionDate]), 
            'Cases'[CompletionDate] >= DATE(2018, 02, 12)
        )
    )
)

The numbers being put out by my measure are correct as I have checked them against the data in SQL. The trend is supposed to be over the whole day and is separated by hour. I have been using an Area chart to practice on and visualise what is happening and all I get is the data at the very start of the day rather than at 24 different points.

I think something might be wrong with the relationships, I can't seem to work out if the Time table (formatted HH:00) is linked in correctly to 'Cases' (the table my measure relates to) and if not, what it links into. Currently, I have set up a calculated column with the hour of the CompletionDate rounded down and linked the my Time table into that.

Can someone figure out where I have gone wrong?

Many thanks

Dan

[Anonymous]

Without sample data and/or a data model schema it's impossible to give you an answer.

[Anonymous]

How do I best share that? I can't seem to find a way on the reply.

[Anonymous]

A link to a file shared somewhere.

If they are few records, you can write on the message.

An image of model view

[Anonymous]

https://drive.google.com/open?id=1sOXAA2GdVmtlVXNdRBcEbatCBfDkAwKi

[v-yuta-msft]

@Anonymous ,

---

The numbers being put out by my measure are correct as I have checked them against the data in SQL. The trend is supposed to be over the whole day and is separated by hour. I have been using an Area chart to practice on and visualise what is happening and all I get is the data at the very start of the day rather than at 24 different points.

I think something might be wrong with the relationships, I can't seem to work out if the Time table (formatted HH:00) is linked in correctly to 'Cases' (the table my measure relates to) and if not, what it links into. Currently, I have set up a calculated column with the hour of the CompletionDate rounded down and linked the my Time table into that.

---

Could you share some sample data and the relashiotiop structure between 'Cases', 'Time' and other tables?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Any update on this?

[Anonymous]

Hi @v-yuta-msft,

I sent you a private message with a reduced data set as I can't legally give you the whole file.

Many thanks!

Dan