Forum Discussion

rishari's avatar
rishari
Frequent Visitor
3 years ago

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 (    ...
Need Help
Show and Tell
Alex_Sawdo's avatar
Alex_Sawdo
Icon for Resolver II rankResolver II
3 years ago

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's avatar
rishari
Frequent Visitor
3 years ago

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

  • Keegan_Patton's avatar
    Keegan_Patton
    Icon for Advocate II rankAdvocate II
    3 years ago

    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's avatar
      rishari
      Frequent Visitor
      3 years ago

      Hi Keegan_Patton - Do you mean this ?

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