Metric not filtering by date

[Anonymous]

Hi All!

I've loaded 3 tables and joined them. But I have the problem that one metric doesn't get filtered by date. The Cost metric.

I don't know what I'm doing wrong.

This table is using Order Date from OMS table and Cost from Google Ads table.

The 3 tables are

• OMS
• Google Analytics
• Google Ads

And the relationships are

OMS with Google Analytics

Order Date <-> Order Date

Order ID <-> Order ID

Google Analytics with Google Ads

Google Ads Campaign ID <-> Google Ads Campaign ID

OMS with Google Ads

Order Date <-> Order Date

[v-gizhi-msft]

Hi,

Please try to change the relationship marked by red circle to inactive and the relationship marked by blue circle to active.

Hope this helps.

Best Regards,

Giotto

[Anonymous]

Hi

Thanks but there is no image attached to understand what you are saying.

[Greg_Deckler]

Um, that's an ugly data model, no offense. The issue is likely the two bidirectional many-to-many relationships you have between those two tables. The direct many-to-many is inactive. You could try it using USERELATIONSHIP but I would strongly recommend refactoring your data model and getting rid of many-to-many relationships by putting bridge tables between them.

[Anonymous]

Thanks!

I'm kind of you new with powerBI and I've never done more than one relationship between tables.

I've looked on the internet about the Bridge Tables and under PowerBI documentation, it says this used to be a workaround but not needed anymore. See your self https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

[v-gizhi-msft]

Hi,

I am sorry that i have forgotten attaching the img, please see this:

Best Regards,

Giotto

[Anonymous]

Hi

I've tried your solution but it didn't work.

But you gave me one idea and I've change a few things.

Link to Now Order ID works. Link to Campaign ID works.

The problem I have is that the Order Date from Google Ads table is not linked to Append1. PowerBI doesn't allow me an active relationship.

I tried both. In Red, a direct connection between tables. In green using a Bridge Table.

[v-gizhi-msft]

Hi,

Please try to change the relationship marked by green circle to inactive and change the relationship marked by aqua circle to active.

Best Regards,

Giotto

[Anonymous]

Thanks, Giotto

I've applied your suggestion.

The problem now is that

1. I can't filter by Google Ads table Campaign Type. Before I was able to select the Campaign Type, and the Campaign ID was linked to Data Studio table, the Orders IDs got filtered.
2. Cost from Google Ads table doesn't show on my table using Order Date form Append1 table.

[v-gizhi-msft]

Hi,

For your issue 1, you can create a new slicer table to filter 'Google Ads'[Campaign type], try like this:

SlicerTable =
DISTINCT (
    SELECTCOLUMNS ( 'Data Studio', "Campaign ID", 'Data Studio'[Campaign ID] )
)

Then create this table and 'Google Ads' table as one-to-many relationship.

Choose this table's field as a slicer, and you can filter Campaign Type by Campaign ID.

For you issue 2, please try to add [Order Date] field from 'Order Date Key' table to your original table visual.

Hope this helps.

if these still do not solve your issue, please share some sample data and relationships(linked columns) between each two tables for me to help you further.

Best Regards,

Giotto

[Anonymous]

Hi Giotto

Here you can find a file with sample data.

Download file 

Thanks for your help

[Anonymous]

Hi @v-gizhi-msft 

Have you seen the file?