none
Distinctcount across star schema with boolean filter

    Question

  • Hi,

    I have a fact table with CustomerNo, DateKey, ProductKey, SupplierKey, Sales.

    This is related to a Calendar table, Product, and Supplier Table.

    I'm trying to calculate a distinct count of customer where the sales = 0 and keeping the original filter conditions on related tables. 

    I've tried :  

    Calculate(Distinctcount(fact[CustomerNo), Fact[Sales]=0) 
    The figures are wrong.  It's hard for me to evaluate what rows are actually being returned due to the size of the table. I think it may be related to the the DistinctCount running a groupby first and the filter being run second.

    So I tried :

    Calculate(Distinctcount(fact[CustomerNo), Filter(Fact, Fact[Sales]=0)) 
    This returns the same value as the one above.

    It's very weird as the slicers on the Pivot do interact with the measure so I know the measures are keeping the existing filter contexts.

    Any ideas what is happening? 


    Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com


    • Edited by Lee Hawthorn Thursday, July 24, 2014 11:10 AM add
    Thursday, July 24, 2014 11:08 AM

Answers

All replies

  • I'm pullling my hair out here and would appreciate help.

    Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com


    • Marked as answer by Lee Hawthorn Thursday, July 24, 2014 12:17 PM
    • Unmarked as answer by Lee Hawthorn Thursday, July 24, 2014 9:49 PM
    • Edited by Lee Hawthorn Thursday, July 24, 2014 9:52 PM follow up
    Thursday, July 24, 2014 12:17 PM
  • well, the formula you provided is correct and should return the correct result
    its hard to say why it would return wrong results without knowing/seeing the data

    the only thing i could think of is the following:
    a customer made two sales 1) with Sales=123 2) with Sales=0
    the formula would of course count that customer as a distinct customer with Sales=0
    maybe thats the reason for your "wrong" results?

    you may also check this link: http://www.daxpatterns.com/distinct-count/

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, July 25, 2014 6:48 AM
    Answerer
  • Your right Gerhard. The reason for the duplicate sales is the time dim. I need to expand the formula to accept 0 sale rows in the whole year. I'll keep plugging away - think I need to && two filters to increase filter context and then count. I feel closer.

    Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com


    Friday, July 25, 2014 7:06 AM
  • I found the answer with Summarize(), Filter() and Countrows() after reading :

    http://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns


    =COUNTROWS(filter(
    SUMMARIZE(Table1
          ,Table1[Year]
          ,Table1[Customer Name] 
          , "Orders", SUM(Table1[Order Amount])
          ),[Orders]=0))


    Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com


    • Marked as answer by Lee Hawthorn Friday, July 25, 2014 11:29 AM
    • Edited by Lee Hawthorn Friday, July 25, 2014 1:30 PM code change
    Friday, July 25, 2014 11:29 AM