Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Optimize DAX Code

Hello everyone, This fourmula count unique number of stores in time window of 42 days backward from the selected date in slicer. However formula runs pretty slow when i change the name of product. ...
ppm1's avatar
ppm1
Solution Sage
3 years ago

Based on your text description, why not just do a distinctcount of stores with a time intelligence expression to go back 42 days? Something like this.

ND Daily =
VAR selDateEnd =
    SELECTEDVALUE ( 'Working Days'[WorkingDate] )
VAR selDateStart = selDateEnd - 42
RETURN
    CALCULATE (
        DISTINCTCOUNT ( WP_FactInput[Code] ),
        WP_FactInput[VisitTime] >= selDateStart
            && WP_FactInput[VisitTime] <= selDateEnd
    )

 

If more complex than that, can you can post a link to a mock version of your pbix with enough data to see slowness (e.g., 1 sec or more)? The community will likely provide alternate faster DAX. 

 

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    ppm1 , first of all, thank you for the quick response. I am attaching a PBI file with Mock data.

     

    I will try to describe the problem in more detail: My Fact table in the model contains transactions that represent visits of employees to various stores. My goal is to calculate a numerical distribution (number of stores where the product selected in the slicer is available) depending on the working day in the month (value in the columns of the matrix visual) with a time window of 42 days backwards from the selected working day. I only need the LAST VISIT TO THE STORE in this context based on the criteria Working Day.

     

    Mock Data PBI Link : https://www.dropbox.com/s/w9mmurd4xypmg7g/Mock%20PBI.pbix?dl=0https://www.dropbox.com/scl/fo/fdnqycmyseujmbekpab27/h?dl=0&rlkey=gld45txq515jujztu0s1l6a7c

     

    • ppm1's avatar
      ppm1
      Solution Sage
      3 years ago

      I looked at this for a bit and ran out of time for tonight. Not sure I fully understand the logic of your existing measure. The measure below is much more performant (76 ms vs 3600 ms) and does what I think you are looking for but does not match all of your current values. If you know your current values are correct, perhaps this approach will give you some ideas to tweak it.

      ND daily 2 =
VAR selDateEnd =
    SELECTEDVALUE ( tSelectedDates[WorkingDate] )
VAR selDateStart = selDateEnd - 42
VAR Summarized =
    CALCULATETABLE (
        SUMMARIZE ( FactND, FactND[StoreID], "cMaxDate", MAX ( FactND[VisitTime] ) ),
        FactND[VisitTime] <= selDateEnd
            && FactND[VisitTime] >= selDateStart
    )
RETURN
    CALCULATE (
        [BaseModelCount],
        TREATAS ( Summarized, FactND[StoreID], FactND[VisitTime] )
    )

      Pat

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        ppm1 , 
        I modified your formula to return the desired result, the trick is in using returning all rows before grouping by the maximum date.

        ND daily 2 = 
VAR selDateEnd =
    SELECTEDVALUE ( tSelectedDates[WorkingDate] )
VAR selDateStart = selDateEnd - 42
VAR Summarized =
    GROUPBY(
       FILTER(ALLEXCEPT(FactND,FactND[ProductID]),FactND[VisitTime]<=selDateEnd&&FactND[VisitTime]>=selDateStart),
       FactND[StoreID],"MaxVisitDate",MAXX(CURRENTGROUP(),FactND[VisitTime]))
  
RETURN
    CALCULATE (
        [BaseModelCount],
        TREATAS ( Summarized, FactND[StoreID], FactND[VisitTime] )
    )