Select a Unique Row from a Group with Master Sequence and Count the Rows

[Anonymous]

H Everyone, Question on Filter Sequence Based Max value

This is how my table Looks currently 

Policy Master	Policy Number	Sequence
1234uw	WCE2144	1
1234uw	WC23333	2
1234uw	WCE222313	4
1234uw	12ERRRR	3
21ECRC	23ECRT	1
212345	2C3444	2

So from the set above i would need to find the Policy Number Associate with each set of Policy Master based on Max of Sequence . 

Then i would have to do a count of all such policies. 

Appreciate if anyone could help on this quick.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may two measures as below.

Count = 
var _policymaster = SELECTEDVALUE('Table'[Policy Master ])
return
CALCULATE(
    DISTINCTCOUNT('Table'[Policy Number ]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Policy Master ]=_policymaster&&
        'Table'[Sequence]=
        CALCULATE(
            MAX('Table'[Sequence]),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Policy Master ]=_policymaster
            )
        )
    )
)

CountTotal = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Policy Master ],
        "Count",
        var x = 'Table'[Policy Master ]
        return
        CALCULATE(
                DISTINCTCOUNT('Table'[Policy Number ]),
                FILTER(
                    ALLSELECTED('Table'),
                    'Table'[Policy Master ]=x&&
                    'Table'[Sequence]=
                    CALCULATE(
                        MAX('Table'[Sequence]),
                        FILTER(
                            ALLSELECTED('Table'),
                            'Table'[Policy Master ]=x
                        )
                    )
                )
        )
    ),
    [Count]
)

Result:

Best Regards

Allan

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

[Pragati11]

Hi @Anonymous ,

You can create two calculated columns as follows:

max Seq = CALCULATE(MAX('policy Data'[Sequence]), FILTER(ALLSELECTED('policy Data'), 'policy Data'[PolicyMaster] = EARLIER('policy Data'[PolicyMaster])))

PolicyOnMaxSeq = CALCULATE(MAX('policy Data'[Policy Number]), FILTER(ALLSELECTED('policy Data'), 'policy Data'[max Seq] = EARLIER('policy Data'[max Seq]) && 'policy Data'[PolicyMaster] = EARLIER('policy Data'[PolicyMaster])))

I used the sample data from your screenshot and I get the following result:

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak]

@Anonymous 

create a desc rank policy master sequence and filter on 1

For Rank Refer these links

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
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-3-of-3-the-finale

The file is created after the signature. Created as column

[Anonymous]

@amitchandak Thanks for the quick response,

while it did answer on how i can pull a unique row from the Rank Function.

I'm still not sure on how to take a count of  Policy number (Policy Number from my example table )  from the derived unique row for the output of Rank function.

[Pragati11]

Hi @Anonymous ,

Can you check my response if that helps you?

Thanks,

Pragati