Dax filter() performance

[jatneerjat]

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

[v-jiascu-msft]

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]

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

Could anybody help me on this?

[v-jiascu-msft]

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]

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]

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