Hello,
I am making my first attempt at a complex Dax query to produce a table of results. I've hit a few bumps a long the way and progress was mostly promising, but I've hit a wall. I'm trying to reproduce a complex SQL stored procedure as a DAX query instead (the users of the query will no longer have sql access and need variable inputs which is why I am pursuing this method).
Below is the DAX query I have so far, hopefully the comments make it understandable. I'm happy to hear feedback on whether my approach is wrong, I feel like I'm about 1 step away from the result I'm after but just can't seem to get it across the line. Please feel free to critique any of the query I've written and advise how it could be improved.
// DAX Query
DEFINE
//The following VARs are defined in this way because they originated as What-If Parameters in Power Bi
// These are the dynamic user input variables for the query
VAR _UpperBound =
FILTER(
KEEPFILTERS(VALUES('Upper Bound'[Upper Bound])),
'Upper Bound'[Upper Bound] = 559000
)
VAR _LowerBound =
FILTER(
KEEPFILTERS(VALUES('Lower Bound'[Lower Bound])),
'Lower Bound'[Lower Bound] = 204000
)
VAR _C_ClassReplen =
FILTER(
KEEPFILTERS(
VALUES('C Class Replenishment (days)'[C Class Replenishment (days)])
),
'C Class Replenishment (days)'[C Class Replenishment (days)] = 7
)
VAR _B_ClassReplen =
FILTER(
KEEPFILTERS(VALUES('B Class Replenishment'[B Class Replenishment])),
'B Class Replenishment'[B Class Replenishment] = 3
)
VAR _A_ClassReplen =
FILTER(
KEEPFILTERS(VALUES('A Class Replenishment'[A Class Replenishment])),
'A Class Replenishment'[A Class Replenishment] = 1
)
// Determine which Class each product falls into based on its Annual Use (Fact Table 1)
VAR _UsageClass =
SUMMARIZECOLUMNS(
Products[SalesCode],
_UpperBound, // these add in the bound context for use in the measure
_LowerBound,
"SumDailyUsage", CALCULATE(SUM('Flowline Station Usage'[DailyUsage])),
"Class", 'Products'[Class],
"SumUsage", CALCULATE(SUM('Annual Usage Value'[Usage]))
)
// Based On Class, Determine the replenishment period
VAR _ReplenishmentPeriod =
SUMMARIZECOLUMNS(
Products[SalesCode],
_UpperBound,
_LowerBound,
_UsageClass,
_C_ClassReplen,
_B_ClassReplen,
_A_ClassReplen,
"Replen", [Replenishment Period]
)
// Calculate the period use for each day of use, looking ahead by the number of replenishment days for the product's class
var _PeriodUsage =
filter(
KEEPFILTERS(
SUMMARIZECOLUMNS(
Products[SalesCode],
'Flowline Stations'[StationId],
Dates[Date],
_ReplenishmentPeriod,
_UpperBound,
_LowerBound,
_UsageClass,
_C_ClassReplen,
_B_ClassReplen,
_A_ClassReplen,
"DailyUsage", sum('Flowline Station Usage'[DailyUsage]),
"PeriodUsage", [Period Usage]
)
),
not(ISBLANK([DailyUsage]))
)
// Rank each period use for a product at a station Asc and Desc
var _RankedUsage =
ADDCOLUMNS(
'_PeriodUsage',
"Rank",
RANKX(filter(_PeriodUsage, Products[SalesCode] == EARLIER(Products[SalesCode]) && 'Flowline Stations'[StationId] == EARLIER('Flowline Stations'[StationId])), [PeriodUsage],,asc, dense),
"ReverseRank",
RANKX(filter(_PeriodUsage, Products[SalesCode] == EARLIER(Products[SalesCode]) && 'Flowline Stations'[StationId] == EARLIER('Flowline Stations'[StationId])), [PeriodUsage],,desc, dense)
)
// Find the rank at the 95th percentile of all ranks for each product/station
table _ThresholdRank =
ADDCOLUMNS(
FILTER(_RankedUsage,
[ReverseRank] == 1),
"ThresholdRank", roundup([Rank]*0.95, 0)
)
// **Missing Step** - Create a list of Period Usage for each product/Station where the rank = the 95%ile rank above
EVALUATE
// ??
TIA for any help & advice
