cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Line Chart not plotting for all Dates

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'.
1 ACCEPTED SOLUTION
Community Support

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])))

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Anonymous
Not applicable

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
Not applicable

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.

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

Community Support

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])))

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msft perfect, thank you!

Anonymous
Not applicable

Sorry the above was for @v-lili6-msft

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors