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]=...
Need Help
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"
)

  • jatneerjat's avatar
    jatneerjat
    Helper V
    7 years ago

    Could anybody help me on this?

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

      Hi jatneerjat,

       

      Can you post a dummy sample? AND the goals of each formula. For example, we add ALL to clear the context for a purpose. So we can't remove it directly unless it's necessary. 

       

       

      Best Regards,
      Dale

      • jatneerjat's avatar
        jatneerjat
        Helper V
        7 years ago

        Hi v-jiascu-msft

         

        I was not able to attach excel here in forum so adding the image for sample data.

        this is for the first dax query.

         

         

        Thanks