Help with Rank and filtering DAX

[Anonymous]

I am still new to dax and working on solution for ranking a dataset based on filters user has made and then show the higest rank record in each category .

in below case , I want to rank person_no and p_type based on priority_no  and  only show those records which have rank =1 based on filter selected (filters can be  worker_id ,org_id).

thanks in advance

a_id	person_no	p_type	priority_no	worker_id	org_id
123	1	M	15	ABC	1
143	1	M	16	ABC	1
143	1	N	3	ABC	1
164	1	N	4	ABC	1
167	2	M	4	ABC	1

[themistoklis]

@Anonymous 

Try the following formula to add the ranking:

Sorting Rank =
VAR current_person_no =
    MAX ( Sheet1[person_no] )
VAR current_type =
    MAX ( Sheet1[p_type] )
RETURN
    RANKX (
        FILTER (
            ALLSELECTED ( Sheet1 ),
            Sheet1[person_no] = current_person_no
                && Sheet1[p_type] = current_type
        ),
        CALCULATE ( SUM ( Sheet1[priority_no] ) ),
        ,
        DESC,
        DENSE
    )

Then on filter section filter when 'Sorting Rank' = 1