Measure filtering within Graphs not functioning as expected

JamesLindsay101 — Wed, 25 May 2022 11:11:30 GMT

Hi,

I have an issue where a measure is being used in a horizontal bar graph; along the Y axis is split out by Department and along the X axis is the measure value for the different departments. The measure when split on the graph seems to be not filtering by each department throughout the whole graph.

The Issue seems to be the graph. For each bar, its not calculating the measure correctly. The bars of the bar graph (see picture below) total the annualised sick ratio (same measure that is used in the graph, see picture at the bottom). Why is this happening? Shouldn't the bars being calculating their own sickness ratio based on the annual sickness ratio measurement below?

From how it's interacting, it looks like the filtering occurring to split up the measure into the bars looks like it's filtering the measure [Past 12 Month Sick Days] but not the [Active Employees] and [Headcount (12 Months Prior)] measures. However, when I use a filter on the page for those departments, it does in fact filter the whole measure, and not produce the issue described above.

In Summary I would like my bars in my bar graph to be calculating their own annual sickness ratio, and I'm unsure if it's because of the relationships put together, or if graphs can't do something that complicated.

The measure(s) is calculated as below:

Annualised Sickness Ratio = ([Past 12 Month Sick Days]/((([Active Employees]+[Headcount (12 Months Prior)])*252)/2))

Where Both [Active Employees] and [Headcount (12 Months Prior] are measures inside a table called Employee Listing and

[Past 12 Month Sick Days] =

VAR SelectedDate=LASTDATE('Date Dimension'[Date])

VAR SelectedDate12MonthsPrior=SAMEPERIODLASTYEAR(SelectedDate)+1

RETURN

CALCULATE(SUM(Absences[In Year Adjusted Total Sick Workdays]),

DATESBETWEEN('Date Dimension'[Date],SelectedDate12MonthsPrior,SelectedDate),

Absences[STS/LTS?]="STS")

Where In Year Adjusted Total Sick Workdays is a measured column part of the absences table, see relationship map below.

NB: I can't share raw data as it's sensitive information. Thanks for your help in advance.

Re: Measure filtering within Graphs not functioning as expected

Icey — Mon, 30 May 2022 08:10:40 GMT

Hi JamesLindsay101 ,

//From how it's interacting, it looks like the filtering occurring to split up the measure into the bars looks like it's filtering the measure [Past 12 Month Sick Days] but not the [Active Employees] and [Headcount (12 Months Prior)] measures. However, when I use a filter on the page for those departments, it does in fact filter the whole measure, and not produce the issue described above.

Do you use the same column for the axis and filter in your scenario?

In addition, try to put "Annualised Sickness Ratio" measure into the bar chart and check if there are any difference compared with the result in the card.

Best Regards,

Icey

topic Re: Measure filtering within Graphs not functioning as expected in DAX Commands and Tips

Measure filtering within Graphs not functioning as expected

Re: Measure filtering within Graphs not functioning as expected