Forum Discussion
Make if statement optimized an total visible
I'm new to DAX. Kindly help me on this.
If i use zero for else statement, I'm getting error as insufficient memory.
I want to make the below dax faster and total to be visible.
DAX =
IF (
SUM ( Counter_flag ) = 1,
CALCULATE ( DISTINCTCOUNTNOBLANK ( ColumnName ),
KEEPFILTERS( 'table[Counter_flag] = 1 )
)
5 Replies
Alex_SawdoResolver II
You can probably re-write your DAX to not use the IF statement, by instead using a FILTER statement in your CALCULATE.
Like this:
CALCULATE(
DISTINTCOUNTNOBLANK([ColumnName]),
FILTER(
'table',
SUM('table'[Counter_flag]) = 1
)
)This one is taking too long to load. (30 million rows are there)
- Keegan_Patton
Advocate II
you may try a combination of SUMX and CALCULATETABLE to acheive your goal and potentially improve performance.
SUMX ( CALCULATETABLE ( VALUES ( [Counter_flag] ), [Counter_flag] = 1 ), SUM ( [Counter_flag] ) )
- Ahmedx
Super User
DAX = VAR _t1 = SUM ( Counter_flag ) VAR _t2 = SUMX(DISTINCT( ColumnName ),1) RETURN IF ( _t1 = 1, CALCULATE (_t2, KEEPFILTERS( 'table[Counter_flag] = 1 ) )
