Forum Discussion
SQL to DAX Converstion - Create complex aggregation measure using conditions
I'm new to PowerBi/DAX and have a burning need to create ~10-15 complex aggregate measures from a related table (Deal Investment) that will be aggregated and stored on another table (Deal). The differences are real, and any help is sincerely appreciated. I am assuming that I will need to use Calculate SUMX and related function syntax, but asking for some newbie guidance from the community.
Here is a sample SQL query that I need to replicate in DAX:
- SQL query Table reference: dit = [Deal Investment Type] ; di = [Deal Investment]; d = [Deal]
- Calculated Column on [Deal Investment] (inserted to SQL below) = [New Money Flag]
CASE WHEN di.new_typeidname IN ('Delayed Draw - Senior Debt', 'RCF') AND di.[New Money flag] = 1 THEN ISNULL(di.new_drawnamountglobal,0)
WHEN dit.new_securitytype = '100000004' and di.new_typeidname NOT IN ('Delayed Draw - Senior Debt', 'RCF','Senior Subordinated Debt') AND di.[new Money flag] = 1 THEN
CASE WHEN di.new_ivpinvestmenttypename IN ('Cash') THEN -1 * ABS(ISNULL(di.new_amount, 0))
ELSE ISNULL(di.new_amount, 0)
END
END
) / 1000000 as total_srsecdebt_amt
3 Replies
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks Greg, point taken! Since my question is really a question of syntax conversion from SQL to DAX for nested Case/When statements, I assumed that providing the relevant table references and SQL logic would suffice. That being said, I will provide a dataset and walk through the calculation logic in plain English. Thanks for taking the time to respond!
