Best method to get a single value answer from a table.

[knotpc]

Hello, I am new to Power BI and building DAX expressions. Excel and pivots where a lot easier but, terrible when it comes to sharing with large groups of people. I need a DAX for Dummies book. 🙂

Problem: I have a Table with the following columns, Account ID (Contains Duplicates), Transaction Count (Numbers), Date of Last Activity (Many Date Duplicated). What I am trying to get is a DISTINCTCOUNT of the Account ID's that have >two Transactions in the last 90 days. Using Excel this was pretty easy using CountIF. But, not so easy in Power BI. I have tried creating MEASURES using CALCULATE and even SUMX I can never seam to get the statements correct. A sample of what I am trying to do is below. 

This probably simple for the experts but, has me baffled. Hoping for some guidance on the best approach to solving this. 

[Sean · Accepted solution]

@knotpc Try this...

Create these 2 Calculated Columns

Time Period = IF( 'Table'[Date]-(TODAY()-90)>=0, "0-90 days", "Older")
Transactions = CALCULATE(COUNTA('Table'[Account ID]), ALLEXCEPT('Table', 'Table'[Account ID]))

 and then this Measure

Measure = CALCULATE(DISTINCTCOUNT('Table'[Account ID]), FILTER('Table', 'Table'[Time Period]="0-90 days" && 'Table'[Transactions]>=2))

Let me know if this works...

[knotpc]

Sean,

I was totally stumped and your solution worked. The Transactions calculated column was the thing I kept missing.

Your awesome.