Forum Discussion

jessset's avatar
jessset
Helper I
6 years ago

Slow DAX Measure when Adding to Filter

Hi All,


I have a matrix table like below:

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

 

The Account Number maps to a table like below:

Account NumberStart DateEnd Date
Account 11 Jan 199931 Dec 2099
Account 21 Jan 199931 March 2019
Account 31 April 201931 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!

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      jessset
      Helper I
      6 years ago

      Thanks Greg, hopefully the revised post above helps!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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",""))
    )

  • jessset's avatar
    jessset
    Helper I
    6 years ago

    Thanks Guys!

     

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