Forum Discussion

Islam's avatar
Islam
Helper V
5 years ago

Many Criterias Calculated columns

Hello Everyone first of all this is a sample for the expected answer but manually       اسلام احمد منصور احمد 1 اسلام احمد منصور احمد 2 30/06/2014 assignment wage 275   02/08/2014 ...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi Islam ,

 

  1. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also

By what you said in Step2,column PreviousRegAddDate.you said you wanna check the corresponding EmpID value,how to check it?Be equal to the value which is filtered in RegdecCodeNum?

For column RegAddBaseWage ,you said:

  1. Then let’s just say the filter resulted 2 rows of data in FTransactions2 table to this particular EmpID then we should get the previous value in in this calculated column itself and for the first value in this column so it should be the Wage column  from dtblBasicEmpInfo table

What do you mean by the previous value,is it from dtblBasicEmpInfo table?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Islam's avatar
Islam
Helper V
5 years ago

ok sir for the first question you asked about step 2 this one 

  1. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also

what i meant here is that i wanna once we find a value in RegDecCodeNum to store the corresponding EmpID in current row in a variable so we can use it as a criteria to filter the FTransactions2 table to get only previous rows not next row

For question two about that step

  1. Then let’s just say the filter resulted 2 rows of data in FTransactions2 table to this particular EmpID then we should get the previous value in in this calculated column itself and for the first value in this column so it should be the Wage column  from dtblBasicEmpInfo table

i mean the base wage for that particular column that will calculate all wage raises based on is going to be the base wage in dtblBasicEmpInfo Table i mean if there is no previous regular adds for the filtered EmpID then we bring the wage from dtblBasicEmpInfo table or else get the previous calculated wage after adding the previous regular add after taking all criterias in this step in consideration of course

  1. If for Current EmpID there is any calculated wage during this year then we should return the last nearest wage from maximum wage of Promotion, Encouragement, exceptional Calculated columns which we are going to calculate later

you can check the table in the post with the expected answer for each employee and you will get it i think

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Islam ,

     

    I do appreciate your patience so far,but it may cost me some more time to figure it out,I will be back to you once I get a result.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

    • Islam's avatar
      Islam
      Helper V
      5 years ago

      ok sir take your time i'll be waiting

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi Islam ,

         

        Sorry for the late reply.

        Per your request,I create a measure to get the result of "PreviousRegAddDate",I'm not sure whether I have understood correctly.

        If RegDecCodeNum has a value,then check the corresponding EmpID value,here I get a result as below:

        Now you need the previous value of each EmpID,which should be as below:

        Then get the nearest RegDecCodeNum ID which is 1,and link the ID in dtblRegularAdds to get the DecImpDate,which is 2015/7/1,for the blank rows,use "Decision Apply Date" value instead.

        If so,use below dax expression:

        Measure = 
var _tab=CALCULATETABLE(VALUES('FTransactions2'[EmpID]),FILTER(ALL(FTransactions2),'FTransactions2'[RegDecCodeNum]<>BLANK()))
var _previousID=CALCULATE(MAX('FTransactions2'[EmpID]),FILTER(ALL('FTransactions2'),'FTransactions2'[EmpID]<MAX('FTransactions2'[EmpID])&&'FTransactions2'[EmpID] in _tab))
var _closestcode=CALCULATE(MIN('FTransactions2'[RegDecCodeNum]),FILTER(ALL('FTransactions2'),'FTransactions2'[RegDecCodeNum]>=_previousID))
Return
IF(ISBLANK(MAX('FTransactions2'[RegDecCodeNum])),BLANK(),
IF(_previousID=BLANK(),MAX('dtblassignmentDec'[Decision Apply Date]),
CALCULATE(MAX('dtblRegularAdds'[DecImpDate]),FILTER('dtblRegularAdds',VALUE('dtblRegularAdds'[RegDecCodeNum])=_closestcode))))

        And you will see:

        If I miss something,pls correct me.

        If it's what you need,let me know,I would provide the dax expression for the other 2 columns.

         

         

        For the related .pbix file,pls see attached.

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!