Count Values Based on On Category A, On Category B, or Both Categories,

[jeffgarlisch]

I have a data set that looks like so 

Merchant ID column contains repeated values for a specific merchant

Transaction ID contains unique values for each transaction

Credit Debit Column Flags whether the transaction is a Credit or a Debit

I'm wondering if there is DAX i could use to calculate the Counts pictured above. 

Count of Merchant doing ONLY Debit transactions

Count of Merchant doing ONLY Credit transactions

Count of Merchant doing BOTH Debit & Credit transactions

Count of All Merchants

This is what I have so far, its a hard value to verify manually querying the database, and I have a feeling the formulas are incorrect!

Counts Total Merchants:

TotalMerchants = DISTINCTCOUNT(DST_TGO[MID])

Counts Total Debit Merchants:

TotalDebitMerchants = CALCULATE(DISTINCTCOUNT(DST_TGO[MID]),CreditDebitCode[IsDebit] = 1)

Counts Total Credit Merchants

TotalCreditMerchants = 
CALCULATE(DISTINCTCOUNT(DST_TGO[MID]),CreditDebitCode[IsDebit] = 0)

Thanks in advance!

[v-lili6-msft · Accepted solution]

Hi@ jeffgarlisch

You need to improve your measure and do them more deeply

Count Combo = [TotalCreditMerchants]+[TotalDebitMerchants]-[TotalMerchants] 
Count Credit = [TotalMerchants]- [TotalDebitMerchants]
Count Debit = [TotalMerchants]- [TotalCreditMerchants]

Result:

Best Regards,

Lin

[jeffgarlisch]

Thanks so much! I dont know why I didnt thinkof that, I must've been too close to the problem!