Forum Discussion
Optimize DAX Code
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
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 )
)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.
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 examplesThis is an algorithm in steps:
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.
Count all unique store numbers for a specific product that I select in the slicer.
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