Forum Discussion
Helper Vhelp on getting a complex index
Helper V
I'm trying to create the following steps measure of indexing a table to get then the top values
1- first we declare a variable holding the last encauragment decision date in the dEncauragementRaises dimension table for each employee
2- then declare a variable to host the assigning decision date in dAssigningDecisions table for each employee also
3- then we define a variable to return the encauragment decision date in the dEncauragementRaises dimension table if exists else we return the assigning decision date in dAssigningDecisions table for each employee also
4- then finally for setting the interval testing period we declare a variable to get the years difference between the calculated date in the previous step variable and the current year encauragment date ( which we can set as a parameter to insert any date as a current year encauragment date) and filter out any employee whose returned difference is less than three years.
5- then for the resulted filtered employees we declare a variable to test vacation existence in three years early to current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) and if there is a vacation with duration more than one year that employee will also be filtered out for each employee also.
6- in the resulted filtered table from the previous step employees we declare two variables to test both normal and court punishment decisions existence in three years early to current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) and if there is any type of punishment with any number of days that employee also will be excluded from our filtered table
7- then we will proceed to sorting the filtered table and the first sorting level will be based on assigning dates or modified assigning date if there is a period add decision for any employee
8- the next sorting level will be those who had a secret report grade of Excellent in the last four previous reports powered up to two total year secret report degree of average past four reports to two yearly reports because reports are calculated semi annually and then those who got in one of the two previous years from current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) a report grade of excellent and other one of efficient and then both previous years are efficient
9- next sort level will be place all employees which never had a previous encouragement add in the same both hierarchy and sub degree level in the organization.
10- next sorting level will be those who haven't got an encouragement add within current sub degree level
11- last step after applying all those sorting stages we should get an index column measure which I’ll sort the final filtered matrix based on it.
I'll attach the fact table and the pbix sample file for making everything clear
Hi Islam ,
Could you please provide some samples to describe your requirements and final expected result in order to get a good understanding? What you want to get finally is to get a index column or something else? Whether your requirements can be simplified?
Best Regards
- Islam
did you check my explaination previous reply
it will be very hard and sophisticating to apply such an example manually but i think by following the steps i provided at my last reply you will get the whole thing
anyway my main goal is to create summerized indexed table to hold 10% of the befining table with all filtering and sorting criterias applied
Hi Islam ,
Sorry that it will be difficult to give you a formula that suits your requirement. I am not very clear about your actual needs and the logic behind it. Especially your model is so complex... I am not clear about the fourth step and subsequent steps you mentioned, what you want to achieve in the end? Could you please provide a simple example to illustrate your requirement at each step and the final desired result? IJust for example:
Employee Name encauragment decision date assigning decision date Vacation decision date court punishment data punishment decision date aa xx xx xx xxx xx bb xx xx xx xxx xx cc xx xx xx xxx xx Desired result:
if encauragment decision date is blank, then display assigning decision date; if xxx then xxx
Employee Name Index aa 1 bb 3 cc 2 Please note that it is not necessary to provide the detailed steps, just provide the sample data, calculation logic(conditions) and your expected result. We will consider what to do to get the result you want based on your expected result...
Best Regards
- Islam
I'm really feeling shamed for not being able to express my whole problem to you Mis but i'll try to give you the logic behind what my goal is specially that first solution you provided me is my promisive and seems like you got the whole problem
any way i made another post with more simplified data and in which i broke down the main goal to small problems making the whole algorith eventually
- Islam
you can go through the whole algorithm step by step
- Islam
I'm trying to create the following steps measure of indexing a table to get then the top values
1- first we declare a variable holding the last encauragment decision date in the dEncauragementRaises dimension table for each employee
2- then declare a variable to host the assigning decision date in dAssigningDecisions table for each employee also
3- then we define a variable to return the latest previous encauragment decision date in the dEncauragementRaises dimension table if exists else we return the assigning decision date in dAssigningDecisions table for each employee also
4- then finally for setting the interval testing period we declare a variable to get the years difference between the calculated date in the previous step variable and the current year encauragment date ( which we can set as a parameter to insert any date as a current year encauragment date) and filter out any employee whose returned difference is less than three years.
5- then for the resulted filtered employees we declare a variable to test vacation existence in three years early to current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) and if there is a vacation with duration more than one year that employee will also be filtered out for each employee also.
6- in the resulted filtered table from the previous step employees we declare two variables to test both normal and court punishment decisions existence in three years early to current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) and if there is any type of punishment with any number of days that employee also will be excluded from our filtered table
7- then we will proceed to sorting the filtered table and the first sorting level will be based on assigning dates or modified assigning date if there is a period add decision for any employee
8- the next sorting level will be those who had a secret report grade of Excellent in the last four previous reports powered up to two total year secret report degree of average past four reports to two yearly reports because reports are calculated semi annually and then those who got in one of the two previous years from current year encouragement date ( which we can set as a parameter to insert any date as a current year encouragement date) a report grade of excellent and other one of efficient and then both previous years are efficient
9- next sort level will be place all employees which never had a previous encouragement add in the same both hierarchy and sub degree level in the organization.
10- next sorting level will be those who haven't got an encouragement add within current sub degree level
11- last step after applying all those sorting stages we should get an index column measure which I’ll sort the final filtered matrix based on it.
I'll attach the fact table and the pbix sample file for making everything clear