Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sivaramakrishnan Gopalan]

Dear All,

I am creating employee headcount and attrition report.I am having the following tables in Report.I am building report using Direct Query mode.

Employee Table  and Date dimension table.Employee Table  structure like below.

EmployeeNumber	Hire Date	TerminatedDate
100	1-Apr-10
200	19-Sep-17	31-Oct-18
300	1-Feb-18

The following are my doubts how to do :

1.I am creating date filter from Date dimension.But in Date dimension table i am having values from 01-APR-2008.While showing widgets values coming from 01-APR-2008 not as 01-APR-2010.
2. In report i am having two pages headcount and attrition.I need date filters for headcount min(hiredate) and attrition min(terminateddate).
3.I have made terminateddate as active relation and Hiredate as passive relation while making relationship with datedimension.

——————————
Sivaramakrishnan Gopalan

——————————

[Vishesh Jain]

Hi ,

Can you please describe your problem more clearly as I am having difficulty understanding the problem.

Also, can you please check the date table, if you have used the function CALENDARAUTO(), this could be a reason why your date table starts from 1st Apr 2008.

You can use the passive/inactive relationship in a table, by using the USERELATIONSHIP() function.

Hope this helps you out.

Thank you,?

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Hi Vishesh,

I am having employee details table and Date dimension table using direct query i am doing the report.
In employee table I am having column Dateofjoining and dateofleaving and Date dimension column making two column reference from employee table.

1.Now i am having slicer  date column from dimension.In dimension table I am having data from 01-APR-2010.But in the employee table minimum employee date of joining date from 01-FEB-2012.How to set min and max for the slicer depends on DOJ and DOL.

2.I have created some measures for Turnover and Retention rate.For All measure I need to do the filter using those columns?.I am testing report with real time data here i am facing data mismatch related to date parameter and measure inforamtion. I will share my working sample today.

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi ,

Is your date table also running on Direct query or have you defined it in your data model?
If you have issues regarding which date the calendar table should start on, then you will have to check the source. If the table is defined in the data model, then you can modify it and make it start on whatever date you want.

As far as measures are concerned, you need to define the DOJ and DOL using the fact table columns only and then on the slicer you can use the dates from the date table. This is a general and an optimal practice.

It would be great if you could please upload some sample along with the desired result, so that we can figure out the right approach to the problem.

Thank you,?

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Hi Vishvesh,
Our approach is Direct Query mode.All the tables are resides in database not imported to PBIX.I have uploaded sample dataset.
In employee sheet DOJ and DOL columns i am making relationship with Date sheet with DateValue column.The Date table will have values before employee dateofjoining. How to set slicer date minimum date of DOJ.

I have written Turnover rate and Retention rate for that I have filtered using Date table date column.Some time the measures mis behaving going for negative.
Little confusion for date slicer.Because I am designing  headcount in one page attrition in another page.

Not able to upload sample PBIX file….

Regards
Siva

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi ,

?To answer your first question, in order to filter the date table based on the minimum date of your DOJ, you will have to create a bridge date table, that is already filtered based on the DOJ. I would not recommend a permanent filter on the main date table as then your calculations might get affected.

This way since your bridge table will have a single column with distinct values, it will have a bi-directional relationship with the main date table, so you can use either one as per your requirement and it will not bloat up your data model either.

As for your second question, I think that is happening probably because your date table contains values, which go even before the min DOJ.
So I would recommend that you put a filter in your measure where you can specify that DateValue >= Min[Employee[Joining Date].

This way all your calculations should take into consideration the Minimum DOJ.

I am afraid this might be as far as I can help you out, unless you upload your sample PBIX file.

I have attached my PBIX for the bridge table solution, so please check it out and I hope this resolves your problems.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Thanks for your solution we will explore and come back.
In our environment we precooked our Date table from Min(Dateofjoining) and as suggested we have filtered necessary DOJ,DOL filters in DAX and its working.

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi ,

I’m glad I could help you out to whatever capacity I could.

If and when everything works out the way you want it to and if you feel I was able to help you out, could please mark the solution as the best answer.?

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Dear Visvesh,
Find the attached pbix in that active employee score card the values remains same if we apply date range filters.
If i have missed something on the DAX.
Also if I execute the  report in Direct Query Some measure query is going on for date conversion.
Also the bar chart refresh taking much time if record set is huge if anything I have missed let me know.

DOJ,DOL,Date table Date column defined as Datetime but during runtime it go for conversion.Due to this if huge data set there is performance delay in refresh.

Sample from Activity monitor
Select columns
from
([t6].[JOINING_DATE] = [t15].[DateValue]))
WHERE
(([t15].[DateValue] IN (CAST( ‘19920207 00:00:00’ AS datetime),CAST( ‘20030610 00:00:00’ AS datetime))

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi ,

I saw your file and I’ll try to answer your questions and my apologies for such a long reply.

Find the attached pbix in that active employee score card the values remains same if we apply date range filters.
If i have missed something on the DAX.
—The active employee score card will remain the same as in most of the years you do not have any ‘Leavers’, so at the end of the year your total will be same. Your formula for active employees is Total Employees – Leavers. Now, since both your total employees and leavers do not change much, there is no change in the Active employee measure.
As far as the scorecard is concerned, in my experience, after playing with the slicer, the values do change. I cannot verify the accuracy of those values, so you will have to check that yourself, but apart from that everything seems to be working.

Also if I execute the  report in Direct Query Some measure query is going on for date conversion.
—Your source (I am guessing its SQL server) might be set to some other date format, probably American or the file setting might be American, which is why it is going for a date conversion. You can go to Options and settings and change the Regional settings to India.
If this still doesn’t work, you will have to check the same in your data source.

Also the bar chart refresh taking much time if record set is huge if anything I have missed let me know.?
—I would have included this in the response to the first question, but I felt it was more apt here.
The best part of measures is that you can define them once and reuse them. So, when you calculate Active employees, instead of applying the filters again, you can simply do Total Employees – Leavers. You have applied the filters in the Leavers measure, so no need applying them again. I have created a new measure Active Employee New in your file and it is giving me the same result, so kindly verify that.

A couple of pointers for best practices.
– Please create a new table for measure, as then you will not have to go hunting for them in the fact/dimension table every single time.
You can just create a new blank table from the Enter data in the Home tab and then add measures to this table. After that you can just remove the column and it will be converted to a measures table.
– If any of your measures is taking a long time to load, you can download DAX Studio from here. This tool will show you which measure is taking how long and then you can optimize the DAX. This should solve your problem of visuals refreshing slowly.
As I said in answer no. 3, when reusing a measure for a new measure, you need not apply the same filters again. If you really need to apply the filters, then I would recommend that you use Variables. This should help reduce the calculation time for the measure and eventually render visuals faster.

I hope I have answered all your questions and I have attached my file as well.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Dear Vishesh,
I need help on the following scenario.I have build headcount report with latest record.But now the requirement is to build using Employee history Data.
In report there is Date range slicer from that I have to choose records from history comparing to Effective date.

Active Employees – Employee should have DOJ on or before To date (and no Date of leaving or date of leaving after To date)
Exit employees – should have date of joining on or before To date and DOL on or after From date

For employee Age,Tenure,Department,Designation these arrived from (effective date  max(SlicerDate)).Through SQL query I have achieved same.But In DAX how to achieve Please help me to over come this.

Attached is the data model for the report.

Regards
Siva

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi ,

I am sorry I am unable to understand your problem.

Can you please upload some sample result that you are looking for, which is derived from your sample data model.

Also I am unable to understand what you mean by ‘To date’?
So if you could please clarify that for me.

Thank you,?

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Sivaramakrishnan Gopalan]

Hi Visesh,
Current requirement is we have to work with Employee history values to find headcount based on selected Date period.Find the attached PBIX and output required.We are facing challenges to get max record for the selected period employee(s).

To Date = Date slicer ToDate (Between slicer in report).Get Latest Record Measure Based on Given Date . We have to calculate age,Tenure  etc dynamically based on date selection and we have to show count of department,Designation,Grade in bar chart in Drill down.We have done this logic in T-SQL but PowerBI Direct Query mode we are facing challenges.If we are facing duplicate records for the selected range from history.Kindly help me to overcome this.

——————————
Sivaramakrishnan Gopalan
Chennai
9444430950
——————————
——————————————-

[Vishesh Jain]

Hi Siva,

I have attached my file.

I don’t know how helpful it is going to be for you but please have a look and let me know.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-