Forum Discussion

amotto11's avatar
amotto11
Helper II
8 years ago

Counting a Rank with Conditions

I have, what i see to be, a very complex problem that i can't seem to even get started with. In my simple example i have 2 tables. One that contains insurance quotes. The table is broken into policyl...
Need Help
OwenAuger's avatar
OwenAuger
Super User
8 years ago

amotto11

Here is an attempt at it:

(pbix uploaded for reference)

 

Note: I have named the tables Quotes and Companies.

 

Quote Count = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
RETURN
    COUNTROWS ( ValidPolicies )
# Rank 1 = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
VAR ValidPoliciesMinPremiums =
    GENERATE (
        ValidPolicies,
        CALCULATETABLE (
            FIRSTNONBLANK ( Quotes[Premium], 0 ),
            ALLSELECTED ( Companies )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Quotes[PolicyLinkID] ), ValidPoliciesMinPremiums )

In both measures, the ValidPolicies variable stores those PolicyLinkID values that have 2+ Companies.

In the # Rank 1 measure, the ValidPoliciesMinPremiums variable contains a two-column table consisting of ValidPolicies paired with the minimum Premium for each policy. This is then used as a filter in order to count the Policies for which the currently filtered Company has the minimum Premium.

 

I have used DISTINCTCOUNT ( Quotes[PolicyLinkID] ) in # Rank 1, just as a precaution in case their were duplicate quotes for the same company on one policy.

 

One question I had was: In your sample outputs, you only showed Quote Count for Companies with nonblank # Rank 1 values. However the Quote Count measure above shows values for all companies that have quotes for the filtered policies, regardless of their # Rank 1 value.

 

Regards,

Owen

amotto11's avatar
amotto11
Helper II
8 years ago

Owen,

 

I think your solution works!!! I had to change the distinctcount to count because Directquery was throwing an error saying something about not being able to use direct query with over x number of rows. I feel confident however there are not duplicate quotes for the same company because my scrubbing of the data in SQL eliminates this. I am going to test some more tomorrow and monday to make sure it is working as intended, but at first glance it appears to be working. With regards to your other question, i have adjusted my original question as my first results table was incorrect as you point out. Thank you again for your help. I will report back weather it works or not when i have tested it to a greater extent.