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]))
ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

Islam 

1. create an index column in pq

2. create two measures

_value1 = 
VAR _max=maxx(FILTER('Table','Table'[EmpID]=MAX('Table'[EmpID])),'Table'[Index])
return maxx(FILTER('Table','Table'[Index]=_max),'Table'[Value1])


_value2 = 
VAR _max=maxx(FILTER('Table','Table'[EmpID]=MAX('Table'[EmpID])),'Table'[Index])
return maxx(FILTER('Table','Table'[Index]=_max),'Table'[value2])

please see the attachment below

  • Islam's avatar
    Islam
    Helper V
    5 years ago

    thx for help sir but i already have an index column embeded in my fact table so can't i add an index column in the resulted summerized table using dax ?

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      Islam 

      if you already have an index column then skip the first step and create measure directly

      • 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