locked
Doubt when counting in one table depending on another RRS feed

  • 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

Answers

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
    Answerer
  • 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 Michael Amadi Wednesday, March 11, 2015 8:53 AM
    • Unproposed as answer by Michael Amadi Friday, March 20, 2015 10:04 AM
    Wednesday, March 11, 2015 7:18 AM
    Answerer
  • 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
    Answerer
  • 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.

    Please send the diagram view of your model.


    Imke

    Thursday, March 12, 2015 10:07 PM
    Answerer
  • 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,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 8:55 AM