Timeline Slicer - how to link it

[Anonymous]

Hey guys,

I would kindly ask you for help again concerning my Dashboard.

I would like to insert a timeline slicer that will change/affect the status of the different services based on the incident dates. The idea would be, that the "MaxStatusText" changes according to it. 0 means, there is no incident, 1 means there is a major and 2 means a critical.

For example if I choose the slicer between 13/11 and 15/11 then the Service with ID "1" should have a Max Status Text with "1", but if I choose the slicer from 09/11 till 12/11 then it should have Max StatusText "0" as it has no incident. (The Service Logoo is just a pictogram based on Max Status Text value)

The formula for the Max StatusText looks like this:

Max StatusText = CALCULATE(MAX('Incident Report'[ASPStatusId]);ALLEXCEPT('ServiceDashboard'; 'ServiceDashboard'[Id])) +0

Do you have any idea, how I could link my timeline slicer so that it changes the status? I tried with the start and end incident date, but did not work out...

Any help is greatly appreciated!

Many thanks,

Adam

[v-lili6-msft]

HI, @Anonymous

Whether table Service Dashboard and table incident Report have a relationship between them?

if so, whether the Cross filter direction is both?

If it is not your case, please share your sample pbix file and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

thank you for your reply. The relationship currently is 1 to many. So 1 Service can have multiple Incidents. Maybe I have to change the relationship in order to solve it?

I uploaded an example File to One Drive, here is the link: 

https://1drv.ms/u/s!Ah2VGOQRBzRVhXLwpYS--ENqRetF

What I would like, to have a slicer that starts from "Service Created". So I only see the Services that has been created before the chosen date. And the Service should have "MaxStatusAsNumber" 0 (green) until an Incident (major or critical) occured. If an Incident occured (Incident Start Date), then the "MaxStatusAsNumber", should change to 1 or 2 until it has an "Incident End Date". If an Incident has both "major" and "critical" paralell (ASPStatusID), then it should show MaxStatusNumber "2"  (red) for that period.

For Example Service ID "3": It should be between 05/10/2018 17:29 until 13/10/2018 19:00 green (Max StatusasNumber "0"), then until 16/10/2018 02:00 red (MaxStatusasNumber "2"), then green again until 14/11/2018 19:00, then yellow (MaxStatusasNumber"1") until 17/11/2018 02:00 and then green again. And with the slicer it should be possible to change the time.

Or Service ID "7" should be green until 09/11/2018 18:00, then yellow until 12/11/2018 18:00 and then red (paralell yellow and green)

Many thanks, you would save my life...:) Any idea is more than welcome!!

Adam

[v-lili6-msft]

hi, @Anonymous

For the 

MaxStatusas Number is calculate column, it couldn't be affected by slicer, and Incident Start Date and Incident End Date are two

different columns. so you need to change column to measure.

After my research, you may try these steps:

Step1:

Create a CALENDAR table

Step2:

Note: don't create a relationship between CALENDAR  table with Incident Report table

Step3:

Use this formula to create a new MaxStatusas Number measure

maxdate = CALCULATE(MAX('CALENDAR'[Date]))

new MaxStatusas Number = CALCULATE(MAX('Incident Report'[ASPStatusID]),ALLEXCEPT('Service Dashboard', 'Service Dashboard'[Id]),FILTER('Incident Report',[maxdate]>='Incident Report'[Incident Start Date].[Date]&&[maxdate]<='Incident Report'[Incident End Date].[Date]))+0

Step4:

Drag field date from CALENDAR  table into timeline slicer

and measure new MaxStatusas Number instead of MaxStatusas Number

here is pbix file, please try it.

https://www.dropbox.com/s/w70i5y53l86cdah/Forum_3.pbix?dl=0

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

many thanks for your quick answer and effort! I checked it qucikly and found out the following ones:

1, For Service ID 3 for example the period 20/09/2018 until 01/11/2018 is chosen it should tell as "newmaxstatusasnumber" 2 instead of "0" as an Incident occured (start date 13/10 and end date 16/10)

2, For Service ID8 as the incident has no end date the "newmaxstatusasnumber" should be from 13/11 (incident starting date) "1".

3,  if a period is chosen where the service was not created, no serive should be shows.

Do you have any idea how to proceed? Many thanks,

Adam

[v-lili6-msft]

hi, @Anonymous

After my research for your 3 questions:

1. For Service ID 3, It ends in 16/10, why for 17/10-01/11 still keep "newmaxstatusasnumber" as 2 , and if after end date 17/11, it keeps "newmaxstatusasnumber" as 0 ?

2. You could use this formula 

new MaxStatusas Number 2 = IF(CALCULATE(MAX('Incident Report'[Incident End Date]))=BLANK(),CALCULATE(MAX('Incident Report'[ASPStatusID]),ALLEXCEPT('Service Dashboard', 'Service Dashboard'[Id]),FILTER('Incident Report',[maxdate]>='Incident Report'[Incident Start Date].[Date]))+0,[new MaxStatusas Number])

3. You could use this formula to create a measure and drag it into visual level filter and set filter is 1

filter date = IF(MAX('Service Dashboard'[Service Created])<CALCULATE(MIN('CALENDAR'[Date])),1,2)

here is pbix, please try it.

https://www.dropbox.com/s/xej3d8gjnoqy1s2/Forum_4.pbix?dl=0

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

thank you again for your support! It's almost totally perfec! I checked it out and found the following:

1, if a service (Service ID 1) has multiple incident (in this case 2) anf of it has an End Date, while the other does not have yet, then the NewmaxStatusasNumber 2 stays "0". For example period 19/11 - 31/12

2, I checked Service ID 7 and the status switches perfectly according to the incident. However if I choose a period, where an Incident happen it should still keep show the highest NewmaxStatusasNumber 2. So for example if we choose period 15/10-31/12 as a critical incident happened during this period(ASP Status ID2) the NewaxStatusasNumber 2 should show "2" instead of "0".

3, Is it possible to have a similar Filter as for the Service Created for the incidents, so if the service was not yet created, also no incident should be shown. First a service has to exist, then the incident.

If you could have again a look I would be suuuuuper grateful!!

Btw where are you from? I'm from Europe, Austria, maybe by any luck you are as well then would buy you a beer!

Many thanks,

Adam

[v-lili6-msft]

hi, @Anonymous

For your questions:

1. You could try this new formula

new MaxStatusas Number 3 = 
IF (
    CALCULATE ( MAX ( 'Incident Report'[Incident End Date] ) ) = BLANK ()
        || (
            CALCULATE ( MAX ( 'Incident Report'[Incident End Date] ) )
                < CALCULATE ( MAX ( 'Incident Report'[Incident Start Date] ) )
                && [maxdate] >= CALCULATE ( MAX ( 'Incident Report'[Incident Start Date] ) )
        ),
    CALCULATE (
        MAX ( 'Incident Report'[ASPStatusID] ),
        ALLEXCEPT ( 'Service Dashboard', 'Service Dashboard'[Id] ),
        FILTER (
            'Incident Report',
            [maxdate] >= 'Incident Report'[Incident Start Date].[Date]
        )
    )
        + 0,
    [new MaxStatusas Number]
)

2. I have a question on this:

for Service ID 7, It end 16/11, why NewaxStatusasNumber is 2, if it is the latest ASP Status ID during selected period?

if so, all the formula need to rewrite the formula logic.

3. you could create a new CALENDAR date table and create a relationship with Service Created, then drag the field into

the second slicer.

here is new pbix file, please try it.

https://www.dropbox.com/s/2da0scs1ui4kmla/Forum_5.pbix?dl=0

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

many thanks again for your fast reply!

If it has no End Date is working properly!

to answer your question: The idea is, that a manager is choosing a time period with the slicer and he sees immediately within the chosen period, if the service had an incident or not. For example, chooses a period from 1/11 - 30/11 and sees that Service ID 7 is red, so had a critical incident. If he chooses a period from 10/10 (when it was created) till 31-10 then he sees that it has no incident so status is green (max status=0)

1, I checked the service created filter, seems that it does not work 😕

2, at service ID 1 the second incident starts on the 18/11, but it shows only from the 19th, guess a =< is missing somewhere?

Many thanks in advance!

Adam

[v-lili6-msft · Accepted solution]

hi, @Anonymous

Sorry for the late reply, for your two problem are due to date time problem.

1. all the date in the service created filter are all date with 12:00:00 AM and the date in Service Created are date with other time

so they are not equal, means that they don't have any relationship.

therefore, you need to create a new date time column

Service Created date = 'Service Dashboard'[Service Created].[Date]

then create relationship by this column instead of service created

2. It also due to this problem

for 18/11

because maxstartdate > maxdate

, therefore, you also need to a new date column like service created column for Incident Start Date and Incident End Date.

then use new date column instead of old column in the formula.

Best Regards,

Lin