Forum Discussion

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
8 years ago

Measure Optimization Help

I think my DAX code would benefit from optimization.  My report takes up to a minute to load visuals after making a slicer selection, and it makes the report unusable.  I'd appreciate any suggestions...
Need Help
jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
7 years ago

Aha!  Thanks Stachu, I appreciate the specific suggestion.  I've just started changing the code over to use CALCULATE(COUNTA() instead of COUNTAX(FILTER() and it appears the time to load after a slicer change has already improved.  I'll follow up once I have all the code changed.

jscottNRG's avatar
jscottNRG
Icon for Helper II rankHelper II
7 years ago

Another question - I'm calculating population statistics to compare to the sliced stats, like this:

 

Population Rough Total Failures = 
COUNTX(FILTER(ALL(RoughInspections_merged), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier]="Fail"), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier])

Any suggestion for how to rework/optimize this using the pattern that Stachu suggested?  CALCULATE(COUNTA(...

 

I've tried this but it's clearly not right:

CALCULATE(COUNTA(ALL(RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier])), ALL(RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier]="Fail"))
  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    if you add ALL inside COUNTA it counts all

    CALCULATE (
    COUNTA ( RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] ),
    FILTER (
        ALL ( RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] ),
        RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] = "Fail"
    )
)
    • jscottNRG's avatar
      jscottNRG
      Icon for Helper II rankHelper II
      7 years ago

      Thanks Stachu.  I believe I entered that measure as you suggested and it appears it's still being affected by slicer selections.  I was hoping for something that is unaffected by slicers.  Any thoughts?

      • jscottNRG's avatar
        jscottNRG
        Icon for Helper II rankHelper II
        7 years ago

        I figured that last issue out, the DAX needed to be this way to get a count unaffected by slicers:

        CALCULATE(COUNTA(RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier]), FILTER(ALL(RoughInspections_merged), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] = "Fail"))

        Note that inside the ALL() function is the entire "RoughInspections_merged" table, not a specific column in that table.