Create calculated column in Table2 that can be sliced using fields from Table1

[jbstephenson]

My objective is to be able to create a visual (a column chart) with Table2[Arbitrary date] as the X-axis and the Table2[Calculated column] as the Y-axis. I need to be able to have this chart affected by slicers from Table1. For example, the calculated column in Table2 should change values when Table1[color] is used as a slicer and "Yellow" is selected. Everything I've researched indicates that a relationship is required (maybe that's not the case), but I don't know how one could create a relationship between the two tables that accomplished my objective. 

Here is an example Table1:

Serial number	Location	Product type	Color	Date received	Date processed	Date shipped
101	TX	Used	Black	10/1/2022	10/31/2022	11/30/2022
102	TX	New	Brown	10/2/2022	11/1/2022	12/1/2022
103	TX	Refurbished	Yellow	10/3/2022	11/2/2022	12/2/2022
104	TX	Used	Pink	10/4/2022	11/3/2022	12/3/2022
105	TX	New	Black	10/5/2022	11/4/2022	12/4/2022
106	AR	Refurbished	Brown	10/6/2022	11/5/2022	12/5/2022
107	AR	Used	Yellow	10/1/2022	10/31/2022	11/30/2022
108	AR	New	Pink	10/2/2022	11/1/2022	12/1/2022
109	AR	Refurbished	Black	10/3/2022	11/2/2022	12/2/2022
110	AR	Used	Brown	10/4/2022	11/3/2022	12/3/2022
111	AR	New	Yellow	10/5/2022	11/4/2022	12/4/2022
112	AR	Refurbished	Pink	10/6/2022	11/5/2022	12/5/2022

Here is an example Table2, column "Calculated column" being the crux of my question:

Arbitrary date	Calculated column
10/2/2022	=calculate(…
10/9/2022	=calculate(…
10/16/2022	=calculate(…

Here is what I've tried (with no success) for the calculated column:

=calculate( count(table1[serial number]) ,table1[date received] > table2[arbitrary date], table1[date processed] > table2[arbitrary date] )

[MFelix]

Hi @jbstephenson ,

To create a relationship between tables you need to follow this guidance.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

But in this case I have some doubts you refer that you want to have a filter on the colour? does this means that you want the calculated table to be dinamic based on selection?

[jbstephenson]

Yes, I need it to be dynamic based on selection

[MFelix · Accepted solution]

Hi @jbstephenson,

I'm this case you need to create a measure to make the calculation dinamically try the following.

Measure =

=countrows(FILTER(table1 ,table1[date received] >MAX( table2[arbitrary date]) & & table1[date processed] > MAX( table2[arbitrary date] )) 

[jbstephenson]

Ah, you're right, thanks! I had also tried a measure using CALCULATE with filter arguments instead of just using FILTER.