Dynamic Ranking Based on Measure Evaluation From Slicer Selection

[Anonymous]

Hi,

I have a sample data table below:

The report I am building has the following requirements:

1) Display the latest "In Force" record per policy number based on Transaction Version( Largest value).

2) "In Force" is defined as 
[Policy Effective] <= [Selected Date] <= [Policy Expiration]
[Transaction Effective Date <= [Selected Date]
*Selected Date is a date slicer on the report page with a fixed start date and an adjustable end date

I have added the following measures to evaluate the different criteria needed:
"PIF Check" Measure: Determines if a given record is "In Force" based on the "Selected Date"

----Rank: Determines the rank of each transaction within each policy number based on the "Transaction Version"

So for example, if the "Selected Date" is 10/24/2022, then the measures currently return as follows.

I am looking for method to only rank transactions where "PIF Check" = 1, so a desired result of:

The end goal being to apply visiual level filters of "PIF Check" = 1 and "Desired Rank" = 1 to only display the latest "In Force" record per Policy Number.

Is there a way to apply a filter of "PIF Check = 1" prior to the execution of the Rank measure?

Also please note that the, table visual will display many policy numbers, so the evaluation will need to be handled for each policy number respectively

[v-xiaosun-msft]

Hi @Anonymous ,

Try add "PIF Check" = EARLIER "PIF Check"  in your filter, then calculate the rankx.

And you can refer to the following link which may be helpful to you.

Solved: Dynamic Ranking Measure - Microsoft Power BI Community

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Unfortunately, getting an error because [PIF Check] is a measure.  Below is the updated RankX measure that is erroring out

 RankX(

            FILTER(

                ALLSELECTED('Append of BR and MTC'),

                'Append of BR and MTC'[Policy Number] = MAX( 'Append of BR and MTC'[Policy Number])

                && [PIF Check] = EARLIER(PIF Check])

            ),

                CALCULATE(

                    MIN('Append of BR and MTC'[Transaction Version])

                )

            ,,

            DESC,

            Skip

        )