rankx dax

Hi,

It would help us if you could tell us what is the logic of your ranking,

and could you please post some datas (without any sensitive data) we can copy and paste.

It should be a RANKX without forgetting to remove filters unwanted with ALL function (at least on Dpt, and dates...).

Hope it helps otherwise please give us more details

powerbifuddaa , try this dax measure

rank_desired = rankx(`TableName`, sumx('TableName', department), 1, Dense)

Hi,

If I understood your needs :

Latest Date in Dpt =
VAR CurrDpt = SELECTEDVALUE( TableEmployee[department] )
RETURN
CALCULATE( LASTDATE( TableEmployee[EndDate] ) , ALL(TableEmployee) , TableEmployee[department]=CurrDpt )

and

Earliest Date in Dpt =
VAR CurrDpt = SELECTEDVALUE( TableEmployee[department] )
RETURN
CALCULATE( FIRSTDATE( TableEmployee[StartDate] ) , ALL(TableEmployee) , TableEmployee[department]=CurrDpt )

you should get this :

Let us know if it works

Nothing to do with RANKX(). Simplest table grouping in PQ does the trick.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBBCsAgDATAv3huINmo1WPfUfz/N2ppU1PE25IdVvA8wxG2ICQEltijCpWeIfeZmZFD20xVa7oSLNk9BrzM8qRgDcrIk9Jviyk7lWJyancvlqWqX/PM6owwmvRHeaA8Gl1PCUVrQMILpa6yX63VuXYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, StartDate = _t, EndDate = _t, Department = _t]),

    Grouped = Table.AddIndexColumn(Table.Group(Source, "Department", {"Grp", each _}, GroupKind.Local),"SN",1,1),
    #"Expanded Grp" = Table.ExpandTableColumn(Grouped, "Grp", {"Customer", "StartDate", "EndDate"}, {"Customer", "StartDate", "EndDate"})
in
    #"Expanded Grp"