Filter for a bar chart with merged tables

[al0981]

Hello,

I have two tables with contacts from two different sources and I created a third table so I could merge the contacts based on their URL since the url for each contact is the same for both sources, this way I can see what contacts from the second source are the same as the ones in the first source.

Connections

This is the model view of my data where K2 Data is the first source and Connected_data is the second source and these are merged in Connected_data.

For my bar chart I created the measure:

Total Data of Phases = COUNTROWS(Distinct(Union(

    VALUES('K2 Data'[id]),

    VALUES(Connected_data[id])

)))

Where this measure is in the Y axis and the name of the stage is in the X axis, this shows all contacts from K2 Data and only the ones from CRM_data that have the same URL as a contact from K2.

Now my problem is that I want to have a slicer that can choose the selection of contacts but if I put the one from K2 only part of the chart is filtered and if I put the one from connected_data the same happens, I tried also putting the 2 but I get an error from that.

Im sorry for the big description but I cant share the dashboard itself since it has sensitive data, any help is very much appreciated.

Thank you

[mark_endicott]

@al0981 - if Connected data is the table that has the contacts from both sources you will need to use this for the slicer. 

You will also need to make sure you use a column that is populated with data that comes from both tables in the slicer. My suspicion is that the column that you have used from Connected_data only has data from this table, and the rows for K2 are blank, hence why your filter is only applied to half the visual. 

If that's not the case, it's going to be your relationships, and specifically it will be the fact that the relationship from CRM Data to Stage only works in one direction. You can try editing your measure to something like the below:

Total Data of Phases = 

CALCULATE(
COUNTROWS(Distinct(Union(
    VALUES('K2 Data'[id]),
    VALUES(Connected_data[id])
))), CROSSFILTER( CRM_data[relationship column], Stage[relationship column], BOTH)

If this works for you, please accept it as the solution. 

[al0981]

Hi Mark, 

The Connected_data table is the merge of the other 2 tables based on the URL of the contats, so this one will have 10 contacts that are coincident to the two tables while the other tables have hundreds of contacts. 

I tried changing the relationships from connected_data - CRM_data to Connected_data - K2 Data and trying that measure but I just get the contacts from K2 data and the contacts from the other table don't show up at all.