Forum Discussion

jatneerjat's avatar
jatneerjat
Helper V
7 years ago

Dax filter() performance

Hi,

Greg_Deckler Anonymous Stachu

Could i replace below FILTER() function with some other function to improve performance

 

 

CALCULATE(DISTINCTCOUNT(vwFact[AppId])
,FILTER(vwFact,vwFact[Area]="ACQ")
,FILTER(vwDate,vwDate[Date]))

 

or could i simply write it as:

 

CALCULATE(DISTINCTCOUNT(vwFact[AppId])
,vwFact,vwFact[Area]="ACQ"
,vwDate,vwDate[Date])

 

Thanks

Need Help

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi jatneerjat,

     

    There aren't any conditions. So the second filter isn't necessary. ",FILTER(vwDate,vwDate[Date]))". What's your goal? The formula is simple. Is the performance very bad?

     

     

    Best Regards,
    Dale

    • jatneerjat's avatar
      jatneerjat
      Helper V
      7 years ago

      v-jiascu-msft

       

      Yes,the power bi reports takes a lot of time to refresh.there are many measures in my ssas server which mostly use calculate along with filter() so i want a way where i can reduce filter() function because i have read somewhere that if we use filter() more oftenly then it adds to poor performance.

       

      can you help me how to optimize below 3 dax queries:

       

      1).

      AVERAGEX(
      KEEPFILTERS(VALUES('vwFact'[UId])),
      CALCULATE(DISTINCTCOUNT('vwFact'[ApplicationId]))

       

      2).

      could i replace MAX() from 

       

      UniqueMeasure =
      MAX(vwDate[CalDate])-1

       

      to

       

      UniqueMeasure  = 
      DATESBETWEEN(vwDate[CalDate])
      ,LASTDATE(vwDate[CalDate])-1
      ,LASTDATE(vwDate[CalDate])-1)
      , ALL(vwDate[CalDate])
      ,vwFact[Area]="Eng")

       

      3).

      How filters applied here,how ALL() affects the filteration here,could i replace ALL()

       

      EngagementDailyUniqueTeams:= CALCULATE(DISTINCTCOUNT(vwFact[TId])
      ,DATESBETWEEN(vwDate[CalDate]
      ,LASTDATE(vwDate[CalDate])
      ,LASTDATE(vwDate[CalDate]))
      , ALL(vwDate[CalDate])
      ,vwFact[Ar]="Eng"

       

       

      4.)

      What is ALLSELECTED() achieving here:

       

      CALCULATE (DISTINCTCOUNT ( vwFact[UId] )
      ,FILTER ( ALLSELECTED ( vwApplication ), vwApplication [Type] = "AA" )

       

      Thanks
      ,vwFact[Ar]="Eng"
      )