Column for filter is from a different table

[crln-blue]

Hello!

I have a calculated measure where its filters will base on another table. I looked this up and some results says that I should merge the two tables. I did and it is taking too much time and I think it's not right. Below are my tables:

AR Table (simplified table):

Customer Name	G/L	Category1	Category2	Date1	Date2	Arrears Date	Arrears Date 2	Amount
						(Custom Column from Power Query)	(Arrears Date minus 1 year)

Sales Table (simplified table):

Customer Name	Category1	Category2	Date1	Posting Date	LC Amount

The only key to them is thru the Customer Name Column. Almost all of my measures are from AR data mainly because this is my main table and this is where Arrears Date is (used in all my visualizations). But I have one measure where its filters are from both of the tables: 

Sales GL = Calculate sum of (sales data) LC Amount when (sales data) Posting Data >= (AR data) Arrears Date && (sales data) Posting Data <= (AR data) Arrears Date 2 && (sales data) Type <> "CC" && (sales data) Type && (sales data) Type <> "DZ"

The domino effect because of Sales GL (all measures from AR data):

DSO = (Measure from AR data/ Sales GL) * 360

Best DSO = Measure from AR data / Sales GL 

ADD = [DSO] - [Best DSO]

Based on past questions & answers from this community, merged table is needed but it takes too much time and I don't think it's right either. I tried replicating Arrears Date to sales data table but it gives one value when using it with the measures from the main table. I believe only one date column must be used in order to get the expected results. 

I also think there's something wrong in my approach? Not sure. But any help is appreciated. Thank you!

[v-stephen-msft · Accepted solution]

Hi @crln-blue ,

Try this measure.

Sales GL = CALCULATE(SUM('Sales'[LC Amount]),FILTER('Sales',[Posting Date]>=MAX('AR'[Arrears Date])&&[Posting Date]<=MAX('AR'[Arrears Date 2])&&[Category1]<>"CC"&&[Category2]<>"DZ"))

 

What does the domino effect caused by Sales GL mean?

Best Regards,

Stephen Tao

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

[crln-blue]

Hello! Thanks for this! I didn't realize that they have to be connected. Will close this thread now.