Hello EveryoneI'm having a trouble creating the following steps measure of indexing a table to get then the top values1- 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 also2- then first sort will be based on assigning dates or modified assigning date if there is a period add decision for any employee3- 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 efficient4- next sort will be place all employees which never had a previous encouragment add along there careers in the company5- next sorting will be those who haven't got an encouragment add within current sub degree level6- 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 itI'll attach the fact table and the pbix sample file for making everything clearthis help is not gonna be paid but it's an excellent excersice for making a full HR system dashboardAny help would be highly appreciated .Thanks in advanceFact Table Sample File

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 () ) 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

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

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

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

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

help on getting a complex index | Microsoft Fabric Community

17 Replies

Islam
Helper V
5 years ago
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
- Anonymous
  Not applicable
  5 years ago
  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 () )
  
  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
  Full test_updated.pbix488 KB
  - Islam
    Helper V
    5 years ago
    really appreciate it sir and i'll test it out and inform you with results

Forum Discussion

help on getting a complex index

17 Replies

Related content

Multiple conditions Index

Index

complex visual using AI

Fixed Index Column

Index Key