ssasdaxssas-tabularbism

Conditional distinct count based on a measure in DAX

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!

Solution

  • 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!