Optimize DAX Code

[Anonymous]

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.
Could you please help me with optimizing my formula?

Thank you in advance!

Best regards
---------------------------------------------------------------------------------

ND Daily =

var selDateEnd = SELECTEDVALUE('Working Days'[WorkingDate])

var selDateStart = selDateEnd - 42

var Summarized = ALL(WP_FactInput[Code],WP_FactInput[VisitTime])

Var tDinamic = FILTER(Summarized,WP_FactInput[VisitTime]<=selDateEnd&&WP_FactInput[VisitTime]>=selDateStart)

var tUpper =

        SUMMARIZE(tDinamic,WP_FactInput[Code],WP_FactInput[VisitTime])

var tLower =

        GROUPBY(

            tDinamic,

           [Code],"VisitTime",MAXX(CURRENTGROUP(),WP_FactInput[VisitTime]))

var Final = INTERSECT(tUpper,tLower)

return

CALCULATE(DISTINCTCOUNT(WP_FactInput[Code]),Final)

[tamerj1]

Hi @Anonymous 

please try

ND Daily =
VAR selDateEnd =
SELECTEDVALUE ( 'Working Days'[WorkingDate] )
VAR selDateStart = selDateEnd - 42
VAR Summarized =
ALL ( WP_FactInput[Code], WP_FactInput[VisitTime] )
VAR tUpper =
FILTER (
Summarized,
WP_FactInput[VisitTime] <= selDateEnd
&& WP_FactInput[VisitTime] >= selDateStart
)
VAR tLower =
GROUPBY (
tUpper,
[Code],
"VisitTime", MAXX ( CURRENTGROUP (), WP_FactInput[VisitTime] )
)
VAR Final =
INTERSECT ( tUpper, tLower )
RETURN
COUNTROWS ( Final )

[Anonymous]

Hi @tamerj1  , I tried this formula. Unfortunately, it's not working, filters from the dimensional tables don't propagate through the measure.

Here's a link to mock data in the attachment : https://www.dropbox.com/s/w9mmurd4xypmg7g/Mock%20PBI.pbix?dl=0

[tamerj1]

Hi @Anonymous 
Please refer to attached sample file with the solution

ND daily = 
SUMX (
        CROSSJOIN ( VALUES ( DimEmployee[EMP Name] ), VALUES ( tSelectedDates[WorkingDate] ) ),
        VAR selDateEnd = tSelectedDates[WorkingDate]
        VAR selDateStart = selDateEnd - 42
        VAR Summarized = SUMMARIZE ( CALCULATETABLE ( FactND ), FactND[StoreID], FactND[VisitTime] )
        VAR tUpper = FILTER ( Summarized, FactND[VisitTime] <= selDateEnd && FactND[VisitTime] >= selDateStart )
        VAR tLower = 
                GROUPBY (
                        tUpper,
                        FactND[StoreID],
                        "VisitTime", MAXX ( CURRENTGROUP(), FactND[VisitTime] )
                )

        VAR Final = INTERSECT ( tUpper, tLower )
        RETURN COUNTROWS ( Final )
)

[Anonymous]

@tamerj1 thank you for your effort,

The formula works quite fast, but it gives a wrong result. It does not calculate the number of unique stores visited within the selected period grouped by the last visit date like in the original attachment i have provided.

[tamerj1]

@Anonymous 
I tried hard to understand your your requirement and the numbers that are generated in your sample file and failed to understand what they really represent. Please clarify with some examples

[Anonymous]

This is an algorithm in steps:

1. Depending on the date in the column in the matrix visual, filter the Fact table and find the last visit for each store (StoreID, SBO column in the dimEmployee table) for a period of 42 days prior to that date.

2. Count all unique store numbers for a specific product that I select in the slicer.

3. The problem arises because in one month, there may be multiple visits to one store, and not all products may be available during each visit. I only need rows with the last visit.

Here is link for excel file:

https://www.dropbox.com/sh/31t6kwe0c885wjt/AAA6R3sYvZr4SuKduYYSwTiBa?dl=0

[ppm1]

Still trying to understand the needed logic and made a page to focus on one result from original matrix. For Product 19, Emp 125, and a 42 day range ending on March 1st (your first column), your measure shows a value of 2. Should the last row below on the 18th be excluded? If not, pls clarify how to count the stores to get 2. Since you are looking for max dates, why doesn't a simple distinct count of stores meet the need?

Pat

[ppm1]

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]

@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/fdnqycm...

[ppm1]

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]

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