Slow DAX Measure when Adding to Filter

[jessset]

Hi All,

I have a matrix table like below:

Account Number	Amount (Measure)	Benchmarked Amount (Measure)
Account 1	$$$	$$$
Account 2	$$$	$$$
Account 3	$$4	$$$

The Account Number maps to a table like below:

Account Number	Start Date	End Date
Account 1	1 Jan 1999	31 Dec 2099
Account 2	1 Jan 1999	31 March 2019
Account 3	1 April 2019	31 Dec 2099

I then have a slicer which allows the user to select a date. When a date is selected, I want to to filter out accounts which are not in between the [Start Date] and [End Date], but with additional criteria. So I have created a measure as below:

Account Filter =
var SelectedDate = MAX('Calendar'[Date])
RETURN
SWITCH(SELECTEDVALUE(Slicer_Period[Period]),
"MTD", IF(DATEDIFF(SelectedDate,MAX(Accounts[End Date]),DAY)<-31,"Y",""),
IF(DATEDIFF(SelectedDate,MAX(Accounts[End Date]),DAY)<-365,"Y",""))

Before I added this measure to the Filter on this Visual, it runs fairly quickly. But when I added the above measure into the filter (where [Account Filter] is "Y"), it becomes very slow.

Any ideas on how to make this quicker (filtering out rows based on a measure)?

Thanks in advance!

[jessset]

Thanks Guys!

But none of the suggested solutions seem to make things quicker... Have a feeling there are too many measures already in place.

[Greg_Deckler]

@jessset I don't suppose you can share the PBIX?

[amitchandak]

@jessset , Try like

Account Filter =
var _max = MAXX(allselected('Calendar'),'Calendar'[Date])
var SelectedDate = DATEDIFF(_max,MAX(Accounts[End Date]),DAY)
RETURN
SWITCH(SELECTEDVALUE(Slicer_Period[Period]),
"MTD", IF(SelectedDate<-31,"Y",""),
IF(DATEDIFF(SelectedDate<-365,"Y",""))
)

[Greg_Deckler]

@jessset OK, a couple things to try. One would be this:

Account Filter =
var SelectedDate = MAX('Calendar'[Date])
RETURN
SWITCH(SELECTEDVALUE(Slicer_Period[Period]),
"MTD", IF( (SelectedDate - MAX(Accounts[End Date])) * 1. <-31,"Y",""),
IF( (SelectedDate - MAX(Accounts[End Date])) * 1. <-365,"Y",""))

Another possible variation:

Account Filter =
var SelectedDate = MAX('Calendar'[Date])
RETURN
SWITCH(SELECTEDVALUE(Slicer_Period[Period]),
"MTD", IF(DATEDIFF(SelectedDate,MAX(Accounts[End Date]),DAY)<-31,1,0),
IF(DATEDIFF(SelectedDate,MAX(Accounts[End Date]),DAY)<-365,1,0))

You could combine these approaches

Trying to think of ways to leverage pre-filtering but not thinking of any off the top of my head. Curious, how many accounts are there unfiltered in the visual?

The general technique you are using I just posted about. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/td-p/1116633

[Greg_Deckler]

@jessset Perhaps try explaining the problem you are trying to solve and there may be a better way of going about it. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jessset]

Thanks Greg, hopefully the revised post above helps!