Nested SQL in a Measure

[michaelshparber]

Hello friends,

I need to calculate monthly sales amount for each customer ONLY IF the customer has a sales transaction of $500 and above.

If there is such a transaction, then I need to summ ALL of that customer's sales in that month.

It's like first selecting customers/month that have this transactions

and then selecting from the sales table joining with the previous results.

I was able to solve it using a calculated column and a measure.

But I am looking for an elegant all-in-one measure solution.

Thank you

Michael

[jthomson · Accepted solution]

So if we have this data:

We'd want bob to be 1500 and dave to be 2000 as they have individual line(s) of at least 500, but jim would be 0 as he doesn't? In that case, this works for me:

if(max('Table'[salevalue])>=500,sum('Table'[salevalue]),blank())

[michaelshparber]

@jthomson 

I think the solution can't be more simple than yours!

Thank you!