Distinctcount across tables with range as condition

[MagnusJ]

I am trying to perform a Distinctount with range as condition across two tables. However, since I have no relations between the tables I am having trouble finding a solution. An example below. I want to create the "Unique in range". 

Any ideas to help me find a solution would be greatly appreciated. 

 

[Anonymous · Accepted solution]

HI @MagnusJ,

You can use below formula to check the matched unique product count:

Unique Count = CALCULATE(COUNTROWS(VALUES('Product'[Product])),FILTER(ALL('Product'),[Xvalue] in GENERATESERIES([MinX],[MaxX],1) && [Yvalue] in GENERATESERIES([MinY],[MaxY],1)))

Result

Notice: I named table1 to range, table2 to product.

Regads,

Xiaoxin Sheng

[MagnusJ]

Thanks for the solution! I am still checking if this is the best one or if I need to combine tables instead. 

[popov]

Hello,

Use this formula to define calculated column

Unique in Range=CALCULATE(DISTINCTCOUNT(Table2[Product]);
Table2[Xvalue]<=EARLIER(Table1[MaxX]) && Table2[Xvalue] >= EARLIER(Table1[MinX]); 

Table2[Yvalue] <= EARLIER(Table1[MaxY]) && Table2[Yvalue] >= EARLIER(Table1[MinY])
)

[MagnusJ]

Thank you for the proposed solution! I would however like to do it without EARLIER as I have a large amount of data. Any ideas on a workaround? 

[popov]

Unfortunatelly, not any ideas on a workaround.