Help with DAX to filter reference table

[magnifybi]

Hey,

I am almost there with a DAX formula whereby I am trying to see whether a customer has had a Sales transaction before, the only problem is there are other lines other than just sales in the table I am looking at, so If a customers first sale was after an initial line that is related to them but not a sale, then this formula isn't picking them up.

A sale is denoted by a "4" in the "First Digit" field, so need to somehow filter the reference table to only look back and check if a sale fo that customer has happened in the past.

(FILTER('QBO General Ledger','QBO General Ledger'[Account Code (Frist Digit) "4"))

New Customer Count =

VAR

Customers = VALUES('QBO Customers'[DisplayName])

RETURN

CALCULATE('QBO Customers'[Customer Count],

FILTER(

Customers,

CALCULATE(COUNTROWS('QBO General Ledger'),

FILTER(ALLSELECTED(Dates), Dates[Date] < MIN(Dates[Date]))) = 0))

Thanks

[amitchandak]

@magnifybi ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

New Customer Count =
CALCULATE(sumx(VALUES('QBO Customers'[DisplayName])), 'QBO Customers'[Customer Count],
FILTER(
Customers,
CALCULATE(COUNTROWS('QBO General Ledger'),
FILTER(ALLSELECTED(Dates), Dates[Date] < MIN(Dates[Date]))) = 0))

[amitchandak]

@magnifybi , no very clear, you can have a filter like

FILTER('QBO General Ledger',left('QBO General Ledger'[Account Code],1) = "4"))

[magnifybi]

Sorry, I have the first digit of the account code set up as a field already, so I don't need to pull back the first digit, this is a column in the data.

What I want is to use that filter in the customer count DAX formula, but I'm not sure where to apply it so that when it looks at the table it only brings back lines where the first digit is "4".

Thanks