Forum Discussion
Solved
Add Ranking Column with filters
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 sort...
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 ) )
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 |
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
)
)Thanks for the solution. This worked perfectly.