Forum Discussion

dapperscavenger's avatar
4 years ago
Solved

Distinct count filtered where distinct count=1

I have two tables, one with orders, and one with the items on the order.  They are linked by a separate key:

 

 

In the example I have:

  • 16 rows of data
  • 7 unique items
  • 14 unique orders
  • 5 unique orders where there is only 1 item

 

I am looking for a measure that will give me that last output. i.e. a distinct count of the order, where the distinct count of the item = 1

 

I tried below but it is gving me a zero output

 

 

CALCULATE( DISTINCTCOUNTNOBLANK(Table1[Order]) , 
FILTER( RELATEDTABLE(Table2), DISTINCTCOUNTNOBLANK(Table2[Item])=1) )

 

 

  • dapperscavenger , Assume two table are related with each other, try a measure like

     

    CALCULATE( Countx(filter(Summarize( Table2, Table1[Order] , "_1" , DISTINCTCOUNTNOBLANK(Table2[Item])),[_1] =1 ),[Order]))

     

    or

     

     

    CALCULATE( Countx(filter(Summarize( Table1, Table1[Order] , "_1" , DISTINCTCOUNTNOBLANK(Table2[Item])),[_1] =1 ),[Order]))

2 Replies

  • dapperscavenger , Assume two table are related with each other, try a measure like

     

    CALCULATE( Countx(filter(Summarize( Table2, Table1[Order] , "_1" , DISTINCTCOUNTNOBLANK(Table2[Item])),[_1] =1 ),[Order]))

     

    or

     

     

    CALCULATE( Countx(filter(Summarize( Table1, Table1[Order] , "_1" , DISTINCTCOUNTNOBLANK(Table2[Item])),[_1] =1 ),[Order]))