Make if statement optimized an total visible

[rishari]

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 )
)

[Ahmedx]

DAX =

VAR _t1 = SUM ( Counter_flag )
VAR _t2 = SUMX(DISTINCT( ColumnName ),1)
RETURN
IF (
      _t1 = 1,
      CALCULATE (_t2,
      KEEPFILTERS( 'table[Counter_flag]  = 1 )
)

[Alex_Sawdo]

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
)
)

[rishari]

This one is taking too long to load. (30 million rows are there)

[Keegan_Patton]

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] )
)

[rishari]

Hi @Keegan_Patton - Do you mean this ?

CALCULATE(
DISTINTCOUNTNOBLANK([ColumnName]),
SUMX (
CALCULATETABLE ( VALUES ( [Counter_flag] ), [Counter_flag] = 1 ),
SUM ( [Counter_flag] )
)
)