Hello!
I have a measure I created that is working as it should. However I'm sure there is a more efficient way of writing this measure that wouldn't be so intensive on the vertipaq engine. This is DirectQuery model FYI.
Any help at all making this measure more efficient would be greatly appreciated!
Thank you
TRUE DFS =
VAR tbl_1 =
FILTER (
ADDCOLUMNS (
SUMMARIZE (
FILTER (
FACT_QUESTION_RESPONSE,
RELATED ( DIM_STORE[Status] )
IN { "active" }
&& RELATED ( DIM_ITEM[Status] )
IN { "active" }
&& RELATED ( DIM_ITEM[UPC] ) <> BLANK ()
&& RELATED ( DIM_QUESTION[Question Type] ) IN { "New Item Distrib" }
),
DIM_DATE[Calendar Date],
DIM_STORE[Store ID],
DIM_STORE[Sub Banner ID],
DIM_ITEM[Item Level ID],
FACT_QUESTION_RESPONSE[In Stock],
FACT_QUESTION_RESPONSE[Valid Response Rank],
DIM_STORE[Sub Banner Description]
),
"@DMS_DATE",
LOOKUPVALUE (
FACT_APL[Retail Availability Date],
FACT_APL[Store ID], FORMAT ( DIM_STORE[Store ID], "General Number" ),
FACT_APL[Item Level ID], DIM_ITEM[Item Level ID]
),
"@SUB+ITEM_ID",
DIM_STORE[Sub Banner ID] & "-" & DIM_ITEM[Item Level ID]
),
[@DMS_Date] <> BLANK ()
)
VAR tbl_2 =
ADDCOLUMNS (
tbl_1,
"@DFS_RANK",
RANKX (
FILTER (
tbl_1,
[@SUB+ITEM_ID] = EARLIER ( [@SUB+ITEM_ID] )
&& FACT_QUESTION_RESPONSE[In Stock] = 1
),
FACT_QUESTION_RESPONSE[Valid Response Rank],
,
ASC,
DENSE
)
)
VAR tbl_3 =
ADDCOLUMNS (
tbl_2,
"@DFS_DATE",
MINX (
FILTER ( tbl_2, [@SUB+ITEM_ID] = EARLIER ( [@SUB+ITEM_ID] ) && [@DFS_RANK] = 3 ),
DIM_DATE[Calendar Date]
)
)
VAR tbl_4 =
ADDCOLUMNS (
tbl_3,
"TRUE_DFS", IF ( [@DFS_DATE] < [@DMS_DATE], [@DMS_DATE], [@DFS_DATE] )
)
RETURN
CALCULATE ( MINX ( tbl_4, [TRUE_DFS] ), FACT_QUESTION_RESPONSE[In Stock] = 1 )
