Correct DAX formula with nested IF and totals calculation

[Anonymous]

Hi All,
I have a simple requirement to calculate the Refund Grand Total, the SQL looks like this:
SELECT
COALESCE(SUM(CASE WHEN (ct_transactions.dataset = 'ticketRefundOrder') THEN case when ct_transactions.currencycode = "CAD" then ct_transactions.grandtotal * 0.72
when ct_transactions.currencycode = "USD" then ct_transactions.grandtotal * 1
when ct_transactions.currencycode = "GBP" then ct_transactions.grandtotal * 0.81
when ct_transactions.currencycode = "EUR" then ct_transactions.grandtotal * 0.92
else 0 End ELSE NULL END), 0) AS ct_transactions_refund_sum_grand_total

I am trying to convert this SQL to DAX in PowerBI as below:

SWITCH(

TRUE (),

('Data Transactions'[Dataset]) = "ticketRefundOrder" && ('Data Transactions'[Currency Code]) = "CAD",
SUMX('Data Transactions',[Grand Total]*0.72) ,

('Data Transactions'[Dataset]) = "ticketRefundOrder" && ('Data Transactions'[Currency Code]) = "USD",
SUMX('Data Transactions',[Grand Total]*1),

('Data Transactions'[Dataset]) = "ticketRefundOrder" && ('Data Transactions'[Currency Code]) = "GBP",
SUMX('Data Transactions',[Grand Total]*0.81),

('Data Transactions'[Dataset]) = "ticketRefundOrder" && ('Data Transactions'[Currency Code]) = "EUR",
SUMX('Data Transactions',[Grand Total]*0.92))

But this gives me an error saying " A single value for column 'Dataset' in table 'Data Transactions' can not be determined.
This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, sum, or count to get a single result."
I have tried using Value/Values('Data Transactions'[Dataset]) but its functionality is to convert a text string to a number which is not right. Also, both the Columns 'Dataset and 'Currency Code' have Data Type as Text.

I am not sure what I am missing here? Can you please help me correct it or give me an alternate way? 

[ebeery · Accepted solution]

@Anonymous the measure needs to be able to determine a single value for 'Data Transactions'[Dataset], otherwise how would it know whether it equals "ticketRefundOrder" or not?

What do you mean when you say that VALUES() is converting a text string to a number?  

Have you tried using SELECTEDVALUE('Data Transactions'[Dataset]) instead?  Have you tried writing this as a calculated column instead of a measure (you would need to re-work the SUMX() portion)

[Anonymous]

Hi @ebeery,
Thanks for replying. Yes, SELECTEDVALUE worked. I knew I had to add something for the Columns 'Dataset and 'Currency Code' but was not sure what exactly.
Thank you.