Forum Discussion

Islam's avatar
Islam
Helper V
5 years ago
Solved

create sorted table and keep duplicates

hello everyone  i have a fact table have duplicated employees ID and wanna sort it with keeping duplicates and then get the last corresponding value from another column in the same table like follow...
Need Help
  • Islam's avatar
    Islam
    5 years ago

    thanks to all who tried to help me out with this but actually it worked for me using the following measures

    Hierarchy Title =
    VAR CurrentHierarchy = SELECTEDVALUE(fTransactions[EmpID])
    RETURN
    MINX(FILTER(ALLSELECTED(fTransactions),fTransactions[EmpID]=CurrentHierarchy),RELATED(dHierarchy[Hierarchy title]))
     
     
    Main Degree Title =
    VAR CurrentMainDegree = SELECTEDVALUE(fTransactions[EmpID])
    RETURN
    MINX(FILTER(ALLSELECTED(fTransactions),fTransactions[EmpID]=CurrentMainDegree),RELATED(dMainDegree[Degree]))
     
     
    Sub Degree Title =
    VAR CurrentSubDegree = SELECTEDVALUE(fTransactions[EmpID])
    RETURN
    MINX(FILTER(ALLSELECTED(fTransactions),fTransactions[EmpID]=CurrentSubDegree),RELATED(dSubDegrees[Sub Degree]))
Islam's avatar
Islam
Helper V
5 years ago

ah ok that's a good idea I'll try this and let you know the results

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

Hi Islam ,

 

Create a measure as below:

_index = IF(MAX('Table'[Index])=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[EmpID]=MAX('Table'[EmpID]))),1,BLANK())

Then put the measure in the filter pane,choose "is not blank":

 

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

  • Islam's avatar
    Islam
    Helper V
    5 years ago

    thanks for your help sir but it gave me an empty values like following picture

     

     

  • Islam's avatar
    Islam
    Helper V
    5 years ago

    i'm getting crazy brother i tested your measure and it gives a perfect answers for your model but for mine it gives me both two SubDegreeID like this image

     

     

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

      Hi Islam ,

       

      Dont worry,you should use "ALL"  instead of "VALUES"."VALUES" is not suitable here.

       

      Best Regards,
      Kelly

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