Line Chart not plotting for all Dates

[Anonymous]

Hi,

I have the following measure that calculates an InjuryRate: 

InjuryRate = Calculate(Countrows(IncidentReports);IncidentReports[Incident Classification]="XYZ")*1000000/[TotalManHour]

So, the InjuryRate values is unique for each date, as the TotalManHour value changes every day. When I put the InjuryRate measure on a Card, the value updates every time I change the date slider. So, there are no problems in calculating this measure and showing the most updated value on a card.

Now, I would like to plot the InjuryRate measure againt time on a line chart. However, the InjuryRate does not calculate for each date value. Instead, it only calculates the value for the two dates that have en entry with Incident Classification=‘XYZ’ and draws a straight line between these two points.

How can I make sure that in the line graph, the InjuryRate measure calculates for each date value?

If it helps, have the following tables:

• A 'DateTable' table with a [Date] column.
• An 'IncidentReports' table with a [Date of Incident] column. The [Date of Incident] is linked to the [Date] column of the 'DateTable'.
• A 'Manpower Summary' with a [Date] column that is used to calculate the [TotalManHour] measure. The Manpower Summary[Date] is again linked to the [Date] column of the 'DateTable'.

[Anonymous]

Hi there - 

Do you mind sharing a sample file of yours for me to look at? I think it will be easier to just look at the file, work on it straight away.

Thanks

[Anonymous]

Hi,

Here's the file that I was working on: https://apiercloud.egnyte.com/dl/NgWLDS7Gzv

You can see that the cards display the correct value when the date is changed in the slider. However, the line chart only plots two values.

[v-lili6-msft]

hi @Anonymous 

Since there are only two date have the data for this measure, you could use a table visual instead of line visual see the details.

and for the value in card visual, it is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If you still have problem, please share your expected output in this sample report.

Regards,

Lin

[Anonymous]

Hi @Anonymous 

Thank you for the response, but I believe this does not really answer my question.

In the table view above, I would like the measure to be calculated for all dates on each row.

So, I would actually like to plot the date against the value that appears in the 'Total' row in the table above.

Based on your responde I tried the below and could get the desired line chart by plotting date against the below measure:

     TestMeasure = TOTALYTD([Long Time Injury Frequency Rate];APier_DateTable[Date])

Which now correctly gives me the below desired line chart:

However, I realise this is only working because all my data is within the 2019 year. This function will no longer work once we start gathering data on 2020.

Is there an alternative way for me to produce the above line chart?

[v-lili6-msft · Accepted solution]

hi @Anonymous 

If so, you may try this formula instead of [TestMeasure]

New TestMeasure = CALCULATE([Long Time Injury Frequency Rate],FILTER(ALL(APier_DateTable),APier_DateTable[Date]<=MAX(APier_DateTable[Date])))

Please replace ',' with ';' when your copy the formula.

Regards,

Lin

[Anonymous]

@v-lili6-msft perfect, thank you!

[Anonymous]

Sorry the above was for @v-lili6-msft