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