Forum Discussion

MagnusJ's avatar
MagnusJ
Frequent Visitor
8 years ago
Solved

Distinctcount across tables with range as condition

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...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
MagnusJ
Frequent Visitor
8 years ago

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