topic Re: Result on works with two slicers??? in DAX Commands and Tips

Result on works with two slicers???

Bfaws — Wed, 03 Jun 2020 19:17:01 GMT

Hi,

I`m looking for some help with some measures that dont seem to be lining up.

I`m trying to calculate the amount of days absence employees have taken in a given period of time per department. I have three tables to base the measures on. The end result I`m after is the amount of days absence divided by the rolling average FTE for the last 12 months.

Table 1: Absences - this shows a list with one line per absence. Each line contains an employee number, type of absence, department, and hours absent

Table 2: Resource reports - these are a snapshot of all departments taken on first day of each month. Its contains a list of all employees and thier FTE (full time equivalent). I have added a measure to this that show the sum of FTE so i know how many full time equavalent employees there are in each period in each department.

Table 3: Rolling calendar

Measure I have so far:

Sum of FTE

12 month rolling average for FTE (FTE for 12 months /12)

Sum of hours: Sum of hours absence per month

Days absent: Sum of hours /7.5 (7.5 equates to one full day)

Days absent PP: This is the Days Absent / 12 month rolling FTE

All these work well in a table with no slicers - when I add costc(T) slicer (department) it only works if i have two slicers (one for absence table and one for resource table) both for Costc(T). For example i only get the correct result when i select HR in each slicer.

Any help would be appreciated. Please see link below to .pbix

Link to .pbix file

Thanks,

Brendan

Re: Result on works with two slicers???

Anonymous — Thu, 04 Jun 2020 03:25:16 GMT

You need to learn the rules of proper dimensional modelling. Check out the attached file.

Best

Re: Result on works with two slicers???

Bfaws — Thu, 04 Jun 2020 06:09:50 GMT

Hi darlove,

Many thanks - This has been replicated in my real model. Thanks for the tip on Dimensional Modelling. I`m really new to Power BI and data modelling in general so any advice is much appreciated.

Thanks,

Brendan

Re: Result on works with two slicers???

Anonymous — Thu, 04 Jun 2020 08:28:56 GMT

Hi Bfaws

Here's something about dimensional modelling for beginners:

https://www.youtube.com/watch?v=fK70mN7r1fE

What applies to the data warehouse applies to models in Power BI as well in its full entirety. "Star schema" is the holy grail of dimensional modelling and you should always, without exceptions, stick to it if you want to save yourself grief and gloom in the future.

Best
D

Re: Result on works with two slicers???

Bfaws — Thu, 04 Jun 2020 08:54:46 GMT

Thanks darlove,

I`ll definately watch the video and look more into the "Star Schema".

Thanks,

Brendan