Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JamesLindsay101
Frequent Visitor

Measure filtering within Graphs not functioning as expected

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.
 
Sickness Issue.JPGPower BI Relationship Picture.JPG
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

View solution in original post

1 REPLY 1
Icey
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors