Issues with relationships

[lifer]

Hi!

I have an issue with a dashboard, where i have 2 different tables with info that i need to be able to filter with 1 same filter.

I made a colum with exactly the same data to identify a data type that i have on both tables so i can make the conection between them, and it work, i was able to filter data from both tables with the same, lets say " data type " but when i try to filter that same data by date, wich both tables have a date colum with the same exact format and everything, im not able to filter both info with 1 date filter ... 

I hope i made my self clear .. but if there is any question please let me know

[Icey]

Hi @lifer ,

Have you tried this?

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @lifer ,

I can't reproduce your issue. Maybe you could give me some sample data.

Best Regards,

Icey

[kentyler]

What sort of relationship do you have between the tables 1 to 1 or 1 to many or many to many ?

Can you post a screen shot of some sample data?

[lifer]

Hi @kentyler  and @Icey 

Thanks for answering! here's some sample data: 

I have create de relationship with the Custome column, and im able to filter by region, sub region and type, but when i try to filter by month, im not able to, since i have to pick up 1 date to filter, i only get the data from the table that i pick up the date to be filtered!

Table 1

Table 2

Target Savings

Date

Region

Type

Sub Region

SV ID( Custome Column )

Total Savings

Date

Region

Sub Region

Type

Buyer Name

Buyer Code

SV ID(Custome Column)

$             500.00

1/1/2019

NA

Recent

US

USRecent

$          488.00

1/1/2019

NA

US

Recent

Alan

C01

USRecent

$             200.00

2/1/2019

NA

Delayed

US

USDelayed

$          198.00

2/1/2019

NA

US

Delayed

Chris

C02

USDelayed

$             300.00

3/1/2019

NA

Recent

Canada

CanadaRecent

$          324.00

3/1/2019

NA

Canada

Recent

Craig

C03

CanadaRecent

$             150.00

4/1/2019

SA

Recent

Mexico

MexicoRecent

$          451.00

4/1/2019

SA

Mexico

Recent

Joseph

C04

MexicoRecent

$             200.00

5/1/2019

SA

Delayed

Argentina

ArgentinaDelayed

$          241.00

5/1/2019

SA

Argentina

Delayed

Joe

C05

ArgentinaDelayed

$             150.00

6/1/2019

SA

Delayed

Mexico

MexicoDelayed

$          351.00

6/1/2019

SA

Mexico

Delayed

George

C06

MexicoDelayed

$             350.00

7/1/2019

SA

Recent

Argentina

ArgentinaRecent

$          487.00

7/1/2019

SA

Argentina

Recent

Mary

C07

ArgentinaRecent

$             400.00

8/1/2019

NA

Recent

Canada

CanadaRecent

$          388.00

8/1/2019

NA

Canada

Recent

Andrew

C08

CanadaRecent

[kentyler]

If I understand you you want to filter both tables by the same date.

You need a third table, a Calendar Table that can have a relationship to both table 1 and table 2 on their date column.

You can find many resources on how to create a calendar table in power bi

Then when you filter on the date in the calendar table, it should filter both table 1 and table 2 to show records with that date.

Here's one helpful link https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

[lifer]

Hi @kentyler 

I have try that already, but the problem is that if i take off the relationship between tables with the custome column that i've created, now im not able to filter by type, or even by region or sub region.

That's been my problem so far

[kentyler]

Do you have one record only for each customer in each table ? You should be able to extract a table of customers, with one record for each customer and then create relationships between that and table 1 and table 2. If you don't want to filter for the same date in both tables you might need to have one calendar table for each of them.

[lifer]

Hi @kentyler 

No, is not 1 record per customer, there is actually around 500k rows but only around 200 buyer names, so there is alot of lines for the same buyer name, and that is my problem, that i cannot filter everything on the same page with 1 data, thats what i've been trying to do, but for some reason the relationship that im making is not working, and its for obvious reason a many to many relationship

[kentyler]

Can you extract the distinct buyer names into a table. Then that table should be in a 1 to many relationship with both table 1 and table 2

[lifer]

Unfortunately no, i cannot, because there is no buyer name on one table, so there is no way to create a relation with that data.

I have tryied to remove the buyer name and buyer code, so i will have almost the same columns, but still not working, im only able to filter 1 table with the date or the region at the same time.