Joining related tables for purposes of filtering

[mangoose736]

I have what seems like a simple problem, but I have so far failed to find an existing solution - apoligies if this exists somewhere already - I cant find it!

I need to display historical revenues from several clients. This is no problem on its own, but these clients had monthly minimum billing rates independent from their actual product usage over the time period. 

Here is the sample minimum table:

Date	Customer	Monthly Minimum
1/1/2021	Customer A	200
1/1/2021	Customer B	500
2/1/2021	Customer A	200
2/1/2021	Customer B	500
3/1/2021	Customer A	200
3/1/2021	Customer B	500
4/1/2021	Customer A	200
4/1/2021	Customer B	500
5/1/2021	Customer A	200
5/1/2021	Customer B	500
6/1/2021	Customer A	200
6/1/2021	Customer B	500
7/1/2021	Customer A	200
7/1/2021	Customer B	500
8/1/2021	Customer A	200
8/1/2021	Customer B	500
9/1/2021	Customer A	200
9/1/2021	Customer B	500
10/1/2021	Customer A	200
11/1/2021	Customer A	200
12/1/2021	Customer A	200

In addition, I have the actual usage data in the following format:

Date	Customer	Product	Rate	Volume	Revenue
1/1/2021	Customer A	Product 1	0.05	733	36.65
1/1/2021	Customer A	Product 2	0.08	491	39.28
1/1/2021	Customer A	Product 3	0.02	724	14.48
2/1/2021	Customer A	Product 1	0.05	556	27.8
2/1/2021	Customer A	Product 2	0.08	495	39.6
2/1/2021	Customer A	Product 3	0.02	713	14.26
3/1/2021	Customer A	Product 1	0.05	322	16.1
3/1/2021	Customer A	Product 2	0.08	632	50.56
3/1/2021	Customer A	Product 3	0.02	487	9.74
4/1/2021	Customer A	Product 1	0.05	239	11.95
sample	data	continues..

I have them joined to a traditional dimdate table like so:

Then I am able to make a visual like this:

which is great - monthly minimum shows up exactly as intended...BUT when i filter by customer, the monthly minimum does not filter with it - for example, here is the same visual for "Customer B" only:

I have tried adding additional relationships but nothing seems to work...is there a best practice for this type of situation?

[Seanan · Accepted solution]

Hi @mangoose736 

What I've done is create a new table to pull in only the customer value from the smpldata table

smpldatarelationship = ALL(smpldata[Customer])

From there I created a relationship between the revmin and newly created table.

Replace your slicer value with the new table 'Customer' column and you will now be able to filter between the different customers with your graph responding correctly.

[mangoose736]

This is the solution - thank you so much!

[Seanan]

Hi @mangoose736 

Have you tried setting the cross filter direction to Both instead of Single? This may fix your issue.

[mangoose736]

Unfortunately no, I get the same results either way - both or single cross filtering. 

I have saved this sample file to onedrive here: https://1drv.ms/u/s!AoFM9U3goeChkbxIF-TWqisTT8iP9Q?e=mdgjJe