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

       

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

        Hi jatneerjat,

         

        It seems the UIDs are unique. So the average could always be 1. What's the expected result of Query 1?

         

         

        Best Regards,
        Dale