topic Re: Add Ranking Column with filters in DAX Commands and Tips

Add Ranking Column with filters

czaldumbide — Sun, 18 Feb 2024 18:02:05 GMT

Hi Everyone,

I need some help creating an index column using DAX. I have a table named ACCOUNTEXCEPTION with columns [ID] , [Key] and [Status]. I first want to create a ranking column, that sorts records in ascending order based on [ID] grouped by [Key].

To achieve this I can use the following code which works perfectly.

Order by key =

VAR CurrentKey = 'ACCOUNTEXCEPTION'[Key]

VAR CurrentID = 'ACCOUNTEXCEPTION'[ID]

RETURN

CALCULATE

(

COUNTROWS('ACCOUNTEXCEPTION'),

FILTER

(

ALL('ACCOUNTEXCEPTION'),

'ACCOUNTEXCEPTION'[Key] = CurrentKey

&& 'ACCOUNTEXCEPTION'[ID] <= CurrentID

)

)

Now the issue I can't seem to solve is how to apply this ranking to only certain keys that meet a criteria. If the first ID within a key has the status = 'Research', then I want to go ahead with the ranking, otherwise I want to leave it blank. Below is how the final table should look like:

Key	ID	Status	Order by Key
A	1	Research	1
A	2	In progress	2
A	3	Completed	3
B	1	Initiated
B	2	In progress
C	1	Research	1
C	2	Completed	2

Any help would be appreciated!
Thanks

Re: Add Ranking Column with filters

Jihwan_Kim — Sun, 18 Feb 2024 19:02:01 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

RANK function (DAX) - DAX | Microsoft Learn

Order by key CC = VAR _condition = COUNTROWS ( FILTER ( ACCOUNTEXCEPTION, ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] ) && ACCOUNTEXCEPTION[ID] = 1 && ACCOUNTEXCEPTION[Status] = "Research" ) ) = 1 RETURN IF ( _condition, RANK ( SKIP, ACCOUNTEXCEPTION, ORDERBY ( ACCOUNTEXCEPTION[ID], ASC ), , PARTITIONBY ( ACCOUNTEXCEPTION[Key] ), MATCHBY ( ACCOUNTEXCEPTION[Key], ACCOUNTEXCEPTION[ID] ) ) )

Re: Add Ranking Column with filters

czaldumbide — Tue, 20 Feb 2024 16:57:25 GMT

Thanks for the code @Jihwan_kin. I need to modify slightly the way my ID column works since it doesnt't always start with a 1 per each key. Please see below the updated example and let me know how we could modify the ranking column. I appreciate your help.

Key	ID	Status	Ranking
A	24	Research	1
A	28	In Progress	2
A	29	Completed	3
B	231	Initiated
B	236	Completed
C	72	Research	1
C	73	In Progress	2
C	77	Completed	3

Re: Add Ranking Column with filters

Jihwan_Kim — Wed, 21 Feb 2024 04:16:47 GMT

Hi,

Please check the below picture and the attached pbix file.

Order by key CC = VAR _firstid = MINX ( FILTER ( ACCOUNTEXCEPTION, ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] ) ), ACCOUNTEXCEPTION[ID] ) VAR _condition = COUNTROWS ( FILTER ( ACCOUNTEXCEPTION, ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] ) && ACCOUNTEXCEPTION[ID] = _firstid && ACCOUNTEXCEPTION[Status] = "Research" ) ) = 1 RETURN IF ( _condition, SUMX ( FILTER ( ACCOUNTEXCEPTION, ACCOUNTEXCEPTION[Key] = EARLIER ( ACCOUNTEXCEPTION[Key] ) && ACCOUNTEXCEPTION[ID] <= EARLIER ( ACCOUNTEXCEPTION[ID] ) ), 1 ) )

Re: Add Ranking Column with filters

czaldumbide — Wed, 21 Feb 2024 19:38:47 GMT

Thanks for the solution. This worked perfectly.