DAX formula working - not when filtered

[EB09]

Hi all,

I have created a relationship between demographic data and leave data as shown below as there were duplicates I couldn't remove in one of my tables.

I'm trying to show a calculation of amounts of leave taken compared to headcount data. (%)

I'm currently using the following formula:

Sick Days to Active Employees Ratio =

DIVIDE(

   'Sick Leave'[Sick Leave Spells Count],

   'Demo link'[Active Employees]

)

'Sick Leave Spells Count' is a measure in 'Sick Leave' table Sick Leave Spells Count = COUNTROWS('Sick Leave')

'Active Employees' is a measure in 'Demo link' table Active Employees =

COUNTROWS(

   FILTER(

      'Demo link',

      'Demo link'[Employee Status (Label)] = "Active"

   )

)

The formula I am using to calculate between the two works fine when entered. But when I apply the filter 'Company' on my report which appeares in both tables I am using, the % just shows 0%. Is there anything I can do to help with this? Is there something wrong with the relationship?

Thanks for any help!

[parry2k · Accepted solution]

@EB09 yes

[EB09]

Ignore me. I've had to change my filter to the 'Company' list from my new 'Companies' table, rather than having my field as' 'Company' from my 'Sick Leave' table. All good 🙂 Grateful for your help!

[EB09]

Unfortunately when I filter by a specific company the measure that I have used still results in saying 0%. Is it to do with my formula?

[parry2k]

@EB09 do you have a company dimension that has a relationship with both the tables? 

[EB09]

Do you mean by doing this?