Conditional distinct count based on a measure in DAX

0
votes

I am trying to do this with DAX and unable to get the correct distinct count...

AccountID   OrderDate   Product     SalesAmount DiscountAmount
1023        Nov-14      Product001  $0          $5.00
1045        Nov-14      Product001  $30.00  $10.00
1023        Nov-14      Product002  $1.00       $0  
1089        Nov-14      Product001  $0          $5.00
2045        Nov-14      Product001  $50.00  $25.00
1045        Nov-14      Product001  $(30.00)    $0

Q1. How do I count Distinct Customers where Total Sales Amount was $0 (Answer is 2, 1045, 1089) - see here I want to ignore 1023 because the total Sales amount for that account is not $0.

I tried to create a measure: 

TotalCustomers:=CALCULATE(DISTINCTCOUNT(Table1[AccountID]), 'Table1')

And then added this: 

ZeroCustomers:=CALCULATE([TotalCustomers], FILTER('Table1', [Sum of SalesAmount]<>0))

But account 1023 gets counted and I get '3' for ZeroCutomers

Q2. How do I add up DiscountAmount for Customers where Total Sales Amount was $0 (Answer $15)

This is an extension to Q1 - once I have figure out Q1 - I can probably figure this one out.

My other choice is to create a new table already summarized by AccountID - but then I wont be able to use slicer since I still want to slice by lets say Product and other Dimensions.

Any help will be appreciated!

Thanks!

2
ssasdaxssas-tabularbism

2 Answers

1
votes

How about:

ZeroCustomers:=CALCULATE(DISTINCTCOUNT(Table1[AccountID]), ALL(Table1), [Sum of SalesAmount]<>0))

ZeroCustomersDiscount:=CALCULATE(SUM([DiscountAmount]), ALL(Table1), [Sum of SalesAmount]<>0))

I think your previous attempt did not work due to how DAX handles filters and groupings. I wish I could give you a more detailed explanation but I am still learning myself.

0
votes

I may have figured it out:

Q1

ZeroCustomers:=COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[AccountID] ),
            "TotalSalesAmount", CALCULATE ( SUM ( [SalesAmount] ) )
        ),
        [TotalSalesAmount] = 0
    )
)

Q2

ZeroCustomerDiscount:=CALCULATE(SUM([DiscountAmount]),
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[AccountID] ),
            "TotalSalesAmount", CALCULATE ( SUM ( [SalesAmount] ) )
        ),
        [TotalSalesAmount] = 0
    )
)

Please comment/reply if you have a better way of accomplishing the same. Hope this helps other!