INTERSECT between 2 Tables with different Filter Values

[kfortenberry]

Hi Everyone, I would appreciate any assistance you could provide.  I have two tables that share a Many to Many relationship. The first is a record of all activities created by our staff and the second is a list of all policies sold. (The actual tables are considerably larger and contain many more columns.)

Activities: need to filter for only TF activities

Client Code	Activity Type	Client Name
A123	TF	Adam
B229	TF	Bob
C234	CT	Charles
E762	NO	David
A123	TF	Adam
B229	NO	Bob
C234	CT	Charles
A123	CT	Adam
E762	TF	David

Policies Sold: need distinct policy numbers

Client Code	Policy Number	Premium
A123	11111	$          100.00
B229	22222	$          300.00
C234	33333	$          200.00
E762	44444	$          900.00
A123	11111	$            50.00
A123	55555	$          400.00

I need to build a formula that shows a count of all policies sold only for those client codes where a TF activity was entered.  If more than one unique Policy Number exists on a client code I need to count each of these, but I would not want to count duplicates of the same policy number on the same client code. 

[DataNinja777]

Hi @kfortenberry ,

Regarding the many-to-many relationship, it appears that you need to create a separate dimension table for Client Code, and create a deta model like below, where many-to-one relatinoships are created for the two fact tables. 

Then, you can write a measure like below:

Activity TF =
CONCATENATEX (
    DISTINCT ( Activities[Activity Type] ),
    IF ( Activities[Activity Type] = "TF", Activities[Activity Type], BLANK () )
)

Which is then brought to the Client Code dimension table,

This is brought through the normal relationship path to the other fact table, Policies Sold.

You can then write a measure like the one below to identify the distinctcount of policy numbers where the client codes have an 'TF' activity type.

Policy Number distinctcount (TF) =
CALCULATE (
    DISTINCTCOUNT ( 'Policies Sold'[Policy Number] ),
    KEEPFILTERS ( 'Policies Sold'[TF] = "TF" )
)

The resulting output is displayed below.

Please let me know if the result is line with your expectations. 

I have attached an example pbix file for your reference.

[Anonymous]

Hi @kfortenberry ,

Have a good day. Have you resolved the issue?

Best Regards,

Wearsky

[SachinNandanwar]

As there exist a many to many relationship across the two tables, create a calcuated column in the Policy table.

Count Of Policies= COUNTX (
    SELECTCOLUMNS (
        FILTER ( RELATEDTABLE ( 'Activities' ), 'Activities'[Activity Type] = "TF" ),
        "Cnt", 'Policy'[Policy Number]
    ),
    'Policy'[Policy Number]
)

[kfortenberry]

Thank you for your assistance. I am receiving the following error when I try to implement this formula:

"A single value for column 'PolicyNumber' in table 'Policy' cannot be determined. Thias can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[SachinNandanwar]

You have to create a calculated column.

[Selva-Salimi]

Hi @kfortenberry 

you can write a measure as follows:

measure count_policy := var tbl1= summarize( filter (activities, [activity type] = "TF") , activities [client code])

var tbl2 = summarize ( filter (policies sold , policies sold [client code] in tbl1) , policies sold [ client code] , policies sold [ policy number]  )

return countrows (tbl2)

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.