Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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 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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |