Forum Discussion
Optimize DAX Code
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 )
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 solutionND 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 examples