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 following

EmpIDValue1value2
1100110
2101111
3102112
1103113
3104114
2105115
4106116
5107117
4108118
5109119

 

and i wanna result table as following

 

1103113
2105115
3104114
4108118
5109119

 

and if there is only one value for any employee then it returns the corresponding values

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

16 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Islam ,

     

    if it is just 2 columns you can achieve that with the LOOKUPVALUE function in DAX.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Islam's avatar
      Islam
      Helper V
      5 years ago

      Unfortunately it's not exactly what i need sir what i need is generating a table with last values for unique employees

  • HarishKM's avatar
    HarishKM
    Super User
    5 years ago

    Islam Hey , 

    Picture 1 . Fact table .

     


    You can use below formula,

    Test Sample = SUMMARIZE(
    'Test table',
    'Test table'[EmpID],
    "value1", MAX('Test table'[Value1]),
    "value2" ,MAX('Test table'[value2]
    ))
    Expected Result using below dax.

     



    if i am able resolve your problem then  Give me kudos and mark it as solution .
    • Islam's avatar
      Islam
      Helper V
      5 years ago

      thanks for help sir but i'm afraid it doesn't completely work for me because when i swap the values for EmpID number 1 for example it gave me 113 and 103 in values although it should give me 110 and 100

  • 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Drag EmpID to the Table visual and write these 2 measures

    Max of value 1 = max(Data[value1])

    Max of value 2 = max(Data[value2])

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

        Hi Islam ,

         

        Is your issue solved now?If so,could you pls mark the reply as answered to close it?

         

         

        Best Regards,
        Kelly

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

  • Islam's avatar
    Islam
    Helper V
    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]))