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 to create the "Unique in range". 

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

 

 

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

5 Replies

  • popov's avatar
    popov
    Resolver III
    8 years ago

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

      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's avatar
        popov
        Resolver III
        8 years ago

        Unfortunatelly, not any ideas on a workaround. 

  • 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.