cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Islam
Helper V
Helper V

help on getting a complex index

 

Hello Everyone
I'm having a trouble creating the following steps measure of indexing a table to get then the top values
1- first we must filter out any employee who had a vacation during the last three years or got a punishment during the same period or got a court punishment during those threeyears also
2- then first sort will be based on assigning dates or modified assigning date if there is a period add decision for any employee
3- the next sorting will be those who had a secret report grade of Excellent in two previous years and then those who got in one of the two previous years from today a report grade of excellent and efficient and then both previous years are efficient
4- next sort will be place all employees which never had a previous encouragment add along there careers in the company
5- next sorting will be those who haven't got an encouragment add within current sub degree level
6- last step after applying all those sorting stages we should get an index column measure which i'll sort the matrix or table based on it
I'll attach the fact table and the pbix sample file for making everything clear
this help is not gonna be paid but it's an excellent excersice for making a full HR system dashboard
Any help would be highly appreciated .Thanks in advance
17 REPLIES 17
Islam
Helper V
Helper V

Any help gentlemen 

as a start i want a measure to only filter out any employee who had a vacation during the last three years over than one year or got a punishment during the same period or got a court punishment during those three years also

yingyinr
Community Support
Community Support

Hi @Islam ,

You can create a measure as below and apply a visual-type filter (Flag=1) to get it. Please find the attachment for the details.

Flag =
VAR _vacation =
    CALCULATE (
        MAX ( 'dEmpBasicInfo'[EmpID] ),
        FILTER (
            'fTransactions',
            'fTransactions'[EmpID] = SELECTEDVALUE ( 'dEmpBasicInfo'[EmpID] )
                && 'fTransactions'[VacationID] <> BLANK ()
        )
    )
VAR _courtpunish =
    CALCULATE (
        MAX ( 'dEmpBasicInfo'[EmpID] ),
        FILTER (
            'fTransactions',
            'fTransactions'[EmpID] = SELECTEDVALUE ( 'dEmpBasicInfo'[EmpID] )
                && 'fTransactions'[CourtPunishmentDecID] <> BLANK ()
        )
    )
VAR _daypunish =
    CALCULATE (
        MAX ( 'dEmpBasicInfo'[EmpID] ),
        FILTER (
            'fTransactions',
            'fTransactions'[EmpID] = SELECTEDVALUE ( 'dEmpBasicInfo'[EmpID] )
                && 'fTransactions'[PunishmedDecID] <> BLANK ()
        )
    )
VAR _flagvacation =
    IF ( NOT ( ISBLANK ( _vacation ) ), 1, 0 )
VAR _flagcourtpunish =
    IF ( NOT ( ISBLANK ( _courtpunish ) ), 1, 0 )
VAR _flagdaypunish =
    IF ( NOT ( ISBLANK ( _daypunish ) ), 1, 0 )
RETURN
    IF (
        _flagvacation = 1
            || _flagcourtpunish = 1
            || _flagdaypunish = 1,
        1,
        BLANK ()
    )

yingyinr_0-1617688772597.png

And please provide more details (examples and expected result with screenshots) if you want to get help about the left requirements.


@Islam wrote:
2- then first sort will be based on assigning dates or modified assigning date if there is a period add decision for any employee
3- the next sorting will be those who had a secret report grade of Excellent in two previous years and then those who got in one of the two previous years from today a report grade of excellent and efficient and then both previous years are efficient
4- next sort will be place all employees which never had a previous encouragment add along there careers in the company
5- next sorting will be those who haven't got an encouragment add within current sub degree level
6- last step after applying all those sorting stages we should get an index column measure which i'll sort the matrix or table based on it

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ok sir here is a video explaining what i need as a first step 

Sample Video 

and this is a captured picture out of it

1.png

in this video and photo i wanna create a filtered table excluding any employee who died or retired or got fired with no return so for employee number 16 for example she got fired then returned to work so she should appear in filtered table whereas employee number 34 got fired then didn't return to work so he should be displayed in filtered table and so on

so for this filtered table i wanna latest or most recent assigned wage ID for wage granularity in the fact table

here are the modified sample files

Sample PBIX 

Sample Fact Excel 

 

 

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

yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

you can go through the whole algorithm step by step

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

yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anither question just to know i wanna filter the fact table based on conditions from different dimension tables which are not related directly I'll upload a video for what i wanna do to my fact table in excel

Hello Experts 

I'm building HR system from scratch and will post every issue i face here for recommending an answer

here is a model which shows a matrix granularity over each year

 

Untitled.png

 

for Employee Number 35 and 61 shouldn't be shown in this matrix because they retired from working so they shouldn't be shown in the matrix and here is the excel table picture showing it

 

1111.png

so what i want here is to show employees whose not retired or died or fired in current date displaying matrix but if any employee returned to work like employee number 16 he should then be shown again in the matrix

here are both Pbix and Excel sample files

Sample BI 

Sample Excel 

 

@yingyinr 

look here Mrs Ying here is the first required measure or method to get a filtered revised table showing wage granularity of inwork employees excluding all employees whose don't match conditions or criterias 

known that my main goal here is to create HR system with Following Tools

Excel 

Excel VBA

Power Query

Power Pivot

to make things clear I'm aiming to create a report of updated employees who should get an encouragement add for current year after filtering out all employees whose don't meet current year encouragement add requirements

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

although this is not the full solution but this measure you provided is awesome sir so i'll reply with more detailed explaination of what exactly i need 

really appreciate it sir and I'm going to try it and inform you with results

really appreciate it sir and i'll test it out and inform you with results

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors