Forum Discussion
SQL CASE Statement to DAX Measure
Yes, sorry I misunderstood your goal. Try this, it should perform much better as well.
TEST = CALCULATE ( SUM ( 'TableName'[Amount] ), 'TableName'[VALUE_TYPE] = "070", 'TableName'[VALUE_TYPE_DETAIL] = "06" )
Thank you for your response jmalone.
However this gives me a 0 in each row as well as a 0 in the grand total.
Moreover, this measure was very slow to populate (I am working with a million records)
Any ideas?
Yes, sorry I misunderstood your goal. Try this, it should perform much better as well.
TEST =
CALCULATE (
SUM ( 'TableName'[Amount] ),
'TableName'[VALUE_TYPE] = "070",
'TableName'[VALUE_TYPE_DETAIL] = "06"
)There are two components:
1) Incorrect totals
2) Slow performance
The totals were incorrect because the first method checks for a condition where the SUM() calculation only happens when those VALUE_TYPE and VALUE_TYPE_DETAIL conditions were met. The engine essentially performed an IF>THEN check for each piece of your visual (ie each row of your table visual), and performed the calc when applicable.
Since the "Total" row had more than the '070' & '06' values, the condition check was = FALSE, and you got no SUM([Amount]).
As a test, if you were to filter (via slicers or page-level filters) the report to show these 070/06 values, the total row should populate with an amount, not 0.
The performance improved because the CALCULATE() function takes full advantage of the engine. The formula filters your table in memory, then applies a SUM() to this virtual table. The engine is designed to perform this type of operation very quickly, even on millions of rows.
The Total row also gives you the correct amount because of the virtual filter that has been applied.
So it's a conditional if/then check (slow) vs. an aggregation on a virtual filtered table (optimized).