create sorted table and keep duplicates

[Islam]

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

EmpID	Value1	value2
1	100	110
2	101	111
3	102	112
1	103	113
3	104	114
2	105	115
4	106	116
5	107	117
4	108	118
5	109	119

and i wanna result table as following

1	103	113
2	105	115
3	104	114
4	108	118
5	109	119

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

[Islam · Accepted solution]

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

[Ashish_Mathur]

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

[Islam]

ok sir I'll try this out

[v-kelly-msft]

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!

[ryan_mayu]

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

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]

@Islam 

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

[Islam]

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

[v-kelly-msft]

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]

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]

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!

[Islam]

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

[HarishKM]

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

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

[selimovd]

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

Blog: WhatTheFact.bi

[Islam]

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