INTERSECT between 2 Tables with different Filter Values

Question

Hi Everyone, I would appreciate any assistance you could provide.&nbsp; 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.)&nbsp;Activities: need to filter for only TF activitiesClient CodeActivity Type&nbsp;Client Name&nbsp;A123TF&nbsp;Adam&nbsp;B229TF&nbsp;Bob&nbsp;C234CT&nbsp;Charles&nbsp;E762NO&nbsp;David&nbsp;A123TF&nbsp;Adam&nbsp;B229NO&nbsp;Bob&nbsp;C234CT&nbsp;Charles&nbsp;A123CT&nbsp;Adam&nbsp;E762TF&nbsp;David&nbsp;&nbsp;Policies Sold: need distinct policy numbersClient CodePolicy Number&nbsp;Premium&nbsp;A12311111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.00B22922222&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300.00C23433333&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200.00E76244444&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900.00A12311111&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50.00A12355555&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400.00&nbsp;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.&nbsp; 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.&nbsp;

selva-salimi · Answer

Hi&nbsp;kfortenberry&nbsp;&nbsp;you can write a measure as follows:&nbsp;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]&nbsp; )&nbsp;return countrows (tbl2)&nbsp;&nbsp;If this post helps, then I would appreciate a thumbs up&nbsp;&nbsp;and mark it as the solution&nbsp;to help the other members find it more quickly.&nbsp;

sachinnandanwar · Answer

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]
)&nbsp;&nbsp;

kfortenberry · Answer

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."