Forum Discussion

kfortenberry's avatar
kfortenberry
Frequent Visitor
1 year ago

INTERSECT between 2 Tables with different Filter Values

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 secon...
"SELECTCOLUMNS"
Filter
Intersect
DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

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.