Rankx with filter ranks all rows

[cdcphist]

Hi,

I have the following data (an example):

value	company	category	rank
50	A	S	37
54	A	S	41
40	A	S	33
33	A	S	30
50	A	S	37
260	A	S	60
295	A	S	63
50	A	S	37
25	A	S	23
17	A	S	18
100	A	S	48
65	A	S	45
	A	S	1
120	A	S	55
	A	S	1
100	A	S	48
16	A	S	16
	A	S	1
	A	S	1
45	A	S	36
150	A	S	57
65	A	S	45
	A	S	1
18	A	S	19
25	A	S	23
20	A	S	20
40	A	S	33
30	A	S	28
15	A	S	14
100	A	S	48
20	A	S	20
280	A	S	62
55	A	S	42
	A	S	1
200	A	S	58
30	A	S	28
	A	S	1
16	A	S	16
100	A	S	48
15	A	S	14
100	A	S	48
60	A	S	43
60	A	S	43
120	A	S	55
10	A	S	13
0	A	S	1
33	A	S	30
40	A	S	33
50	A	S	37
275	A	S	61
250	A	S	59
27	A	S	27
100	A	S	48
25	A	S	23
38	A	S	32
100	A	S	48
25	A	S	23
93	A	S	47
20	A	S	20
	A	S	1
	A	S	1
	A	S	1
	A	S	1

Rank is a calculated column with the following code:

rank = 
VAR currentcompany =table[company]
VAR currentcategory =table[category]
var currentvalue=table[value]
RETURN
RANKX (FILTER ( table, table[company] = currentcompany && table[category] = currentcategory && not(isblank(currentvalue))), table[value_FE],,ASC)

The problem is that even with the filter on the value column, the ranking still  happens on those values. Ideally, i want for some rows to not be ranked at all, specifically, the rows where there is no value. Is this possible?

Thank you.

[Fowmy · Accepted solution]

@cdcphist 

Add the column as follows:

rank = 
VAR currentcompany ='Table'[company]
VAR currentcategory ='Table'[category]
var currentvalue='Table'[value]
RETURN
IF(
    'Table'[value] <> BLANK(),
    RANKX (FILTER ( 'Table', 'Table'[company] = currentcompany && 'Table'[category] = currentcategory && 'Table'[value]<> BLANK()), 'Table'[value],,ASC)
)

[Jihwan_Kim · Accepted solution]

Hi, @cdcphist 

Please try something like the below.

rank CC =
VAR currentC = 'Table'[company]
VAR cuurentCat = 'Table'[category]
RETURN
IF (
ISBLANK ( 'Table'[value] ),
BLANK (),
RANKX (
FILTER (
'Table',
'Table'[company] = currentC
&& 'Table'[category] = cuurentCat
&& NOT ISBLANK ( 'Table'[value] )
),
'Table'[value],
,
ASC
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Fowmy · Accepted solution]

@cdcphist 

Add the column as follows:

rank = 
VAR currentcompany ='Table'[company]
VAR currentcategory ='Table'[category]
var currentvalue='Table'[value]
RETURN
IF(
    'Table'[value] <> BLANK(),
    RANKX (FILTER ( 'Table', 'Table'[company] = currentcompany && 'Table'[category] = currentcategory && 'Table'[value]<> BLANK()), 'Table'[value],,ASC)
)

[amitchandak]

@cdcphist , only a measure can respond to slicer/filter . Create a measure Rank

Try like

Rank =
VAR currentcompany =table[company]
VAR currentcategory =table[category]
var currentvalue=table[value]
RETURN
RANKX (FILTER ( allselected(table[company],table[category]), table[company] = max(table[company]) && not(isblank(table[value]))), calculate(sym(table[value_FE])),,ASC)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns