Relationship & Filtering - Tables Not Filtering as Expected

[markhollandau]

Hi,

I'm having some issues with relationships in my data model. My tables aren't filtering as expected.

Here's a simple example of my set up:

In this set up, I have a Calendar and Campaign Dimension table linking into an Ads Fact table via IDs. Then the Ads Fact table connects out to a Conversions Fact table, linking on a Key I've created. This Key is a concatenation of IDs that match across the Ads and Conversions tables.

I'm expecting the Campaign table to filter the Ads table, which would then filter the Conversions table. But that's not quite happening. Here's what I'm seeing:

In this example, Ad Group Name is a lower level to Campaign. So this Campaign has 3 Ad Groups. The metrics Total Impressions and Total Clicks come from the Ads table. The Total Conversion metric comes from the Conversions table. This behavious is expected - where Impressions and Clicks will repeat for each Conversion. However for this Campaignm I'm seeing a bunch of Conversion IDs that aren't associated with this Campaign, highlighted in yellow. I was expecting this field to filter down based on the relationship set up.

Below is the behavour I'm expecting - to get to this, I've filtered the table using a measure for Row Count > 0 on the Conversion table:

There is another point, which I may need to create a separate post for. As mentioned at the start, there are 3 Ad Groups for this Campaign. One of the Ad Groups doesn't have any conversions, which can happen. But when I add Conversion ID or Conversions, this Ad Group disappears. I'd like this Ad Group to appear with a Conversion ID value of "No Conversions".

Any idea how I can achieve both of these behaviours?

Thanks,

Mark

[TomMartens]

Hey @markhollandau ,

I have an answer for your second question (at least in a simplified model).

Change the behavior of the dimension table in the table visualization to "Show items with no data"

and the row stays in the data visualization:

To provide an answer to your first question, I recommend that you create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the file to OneDrive, Google Drive,  or dropbox and share the link. If you use a spreadsheet to create the sample data instead of the manual input method, share the spreadsheet too.

If I have sample data, I consider it simpler to provide answers to data modeling (here) and DAX questions because this prevents confusion in understanding.

Do not forget to describe your challenge/issue based on the sample data you are providing.

I'm wondering why you have created relationships between the dimension tables (calendar and campaigns) and the conversion fact table, because you state "I'm expecting the Campaign table to filter the Ads table, which would then filter the Conversions table." From my understanding the relationships between dim and conversion are not required.

Here you will find my simplified example that might help to answer your second question: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/Ee2pI8xgUwlFiMLtArQSQXIBDi8yD...

Regards,

Tom

[markhollandau]

Hi @TomMartens ,

I'm using a Calculated Table on my view in order to show and hide fields when they've been selected in a slicer. I've also done this to be able do show and hide fields depending on the page someone is one, so only relevant fields are present. Due to this set up, I can't use the Show Items with No Data option.

I've been given a solution on the following Post that helped me get most of the way there: 

Tables & Relationship: Value for empty IDs when no rows are present 

The only issue I have now is that for all Ad Groups there's an extra blank rows for Conversion ID. Once I get rid of this, I'm sorted.

Thanks,

Mark