Forum Discussion
Best method to get a single value answer from a table.
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.
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...
2 Replies
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...
Sean,
I was totally stumped and your solution worked. The Transactions calculated column was the thing I kept missing.
Your awesome.
