# Doubt when counting in one table depending on another

• ### Question

• Hi i'm new to DAX and in need of clarifying one simple doubt urgently. Imagine that you have 3 tables:
Table Fact Seller      : seller_code, seller_name, manager_code
Table Fact Manager  : manager_code, manager_name
Table Fact Product  : product_code, product_name, seller_code

Relations have been made between keys and should be ok.

I want to calculate the number of active sellers. A Seller is active if it has any (1 or more) product in Fact Product. it would be something like
=CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]);[filter here]))

In [filter here] i need something like FILTER(COUNTROWS('Fact Product';'Fact Product'[manager_code])>=1), to get only the sellers from fact seller who have at least one product in fact product. I think i need to use the keyword VALUES() but i didn't find the correct syntax or the results seem strange or give an error... Also (i think) i can't do a distinct count of sellers in fact product, because next i'll have other filters to consider.

1st tentative
=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[manager_code]>=1;countrows(values('Fact Product'[sk_seller]))>1)
error: "calculation error on 'Fact Seller'[with Listing (Inc)]: expression True/False doesn't specify a column each expression used as a table filter must referentiate exactly 1 column"

2nd tentative
=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[manager_code]>=1;filter('Fact product','Fact Product'[sk_seller])>0)
error: tried filter with 'fact product' and 'fact seller' and the error was "expression not valid or incomplete. review and fix the expression"

3rd tentative
=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[manager_code]>=1; FILTER('Fact Seller', COUNTROWS(RELATEDTABLE('Fact Product')) > 0))
"error: expression not valid or incomplete. review the expression. COUNTROWS syntax is incorrect"

4th tentative
=CALCULATE(COUNTROWS('Fact Seller');'Fact Seller'[manager_code]>=1; FILTER('Fact Seller'; COUNTROWS(RELATEDTABLE('Fact Product')) > 0)) but this crashes my Excel.

Regards

Tuesday, March 10, 2015 6:09 PM

### All replies

• I'd think that this measure in your table Fact Product should bring the no of active sellers:

`ActiveSellers:=DISTINCTCOUNT([seller code])`

Imke

Tuesday, March 10, 2015 8:04 PM
• Yes, but what if i want distinct sellers in fact product ( ActiveSellers:=DISTINCTCOUNT([seller code]) ) that also have orders in orders ?

Thanks

Wednesday, March 11, 2015 6:10 AM
• Where did you hide your order table :-)

I guess it has a many 2 many relationship with your "Fact Product" via your "Fact Seller", which would then actually be a "DIM Seller", having only 1 row per seller.

This will be your code then:

`ActiveSellerMN:=CALCULATE(DISTINCTCOUNT([SellerCode]); FactProduct; FactOrder; DIMSeller)`
Watch this for an explanation of how it works:

https://gbrueckl.wordpress.com/2012/05/08/resolving-many-to-many-relationships-leveraging-dax-cross-table-filtering/

Imke

• Proposed as answer by Wednesday, March 11, 2015 8:53 AM
• Unproposed as answer by Friday, March 20, 2015 10:04 AM
Wednesday, March 11, 2015 7:18 AM
• Yes, I've discovered that is this almost the correct patern,

it should be something like ActiveSellerMN:=CALCULATE(COUNT([SellerCode]); FactProduct; FactOrder)

I'm not totally sure on this syntax, but i would count on dim seller while filtering through both fact tables.

thanks

Thursday, March 12, 2015 6:54 AM
• This measure should sit in your FactProduct Table.

Please try my definition from above - it should work as it is.

Imke

Thursday, March 12, 2015 9:31 AM
• It doesn't work, it remains blank. Bare with me:

Í have dim customer seller (sk_seller,...), fact seller (sk_seller, sk_captain,...), fact product (sk_product, sk_seller, ...), and fact order (sk_order, sk_seller,...)

I think metric should stay in fact seller, as im calculating number of active sellers (sellers with lines in products) and with sales (sellers with lines in orders).

Can you help me out?

Thursday, March 12, 2015 1:49 PM
• This is tough as every time we seem to get new tables here.

Imke

Thursday, March 12, 2015 10:07 PM
• Hi Lrmmf_,

Is this is still an issue? If so please provide a diagram or a complete description of your model as suggested by Imke so that we can help.

Regards,