topic Complex Dax Query Help in DAX Commands and Tips

Complex Dax Query Help

Anonymous — Wed, 03 Nov 2021 15:23:12 GMT

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

Re: Complex Dax Query Help

Greg_Deckler — Wed, 03 Nov 2021 15:33:10 GMT

Anonymous Can you post sample data and expected output?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Complex Dax Query Help

Anonymous — Fri, 05 Nov 2021 14:17:37 GMT

// 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], 'Station Component Stock'[StationStockControlId], 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, 'Station Component Stock'[StationStockControlId] == EARLIER('Station Component Stock'[StationStockControlId])), [PeriodUsage],,asc, dense), "ReverseRank", RANKX(filter(_PeriodUsage, 'Station Component Stock'[StationStockControlId] == EARLIER('Station Component Stock'[StationStockControlId])), [PeriodUsage],,desc, dense) ) table _RankedUsageTbl = _RankedUsage // Find the rank at the 95th percentile of all ranks for each StationStockControlId table _ThresholdRank = ADDCOLUMNS( FILTER(_RankedUsage, [ReverseRank] == 1), "ThresholdRank", roundup([Rank]*0.95, 0) ) // **Missing Step** - Create a list of Period Usage for each StationStockControlId where the rank = the 95%ile rank above

Hi Greg_Deckler

Thank you for the response. I did reply the other day but I've revisited this topic and my latst post is missing? I even received a badge after I sent the reply so no idea how it disappeared!

Anyway, re-writing the reply below:

I'll provide a streamlined part of the query that focuses on the important part. Basically here's waht I'm trying to do:

For each StationStockControlId, Rank the PeriodUsage

Find the Rank at the 95th percentile potision for all PeriodUsage values for each StationStockControlId

Return a list with 1 row per StationStockControlId showing the value at the 95th percentile rank

the below query starts with calculating _PeriodUsage, I've provided a sample output from _PeriodUsage which will let the rest of the query run:

StationStockControlId	Date	DailyUsage	PeriodUsage
165	18/10/2021 00:00	1064	2169
165	25/06/2021 00:00	1064	1204
256	25/06/2021 00:00	1064	1204
256	18/10/2021 00:00	1064	2169
420	18/10/2021 00:00	1064	2169
420	25/06/2021 00:00	1064	1204

The part that has me stumped is how I can filter _RankedUsage by the results of _ThresholdUsage. If I was using sql this would be a join on StationStockControlId and Rank.

Ultimately, I am looking to get the PeriodUsage for each StationStockControlId where the PeriodUsage is ranked at the 95th percentile of all PeriodUsages for that StationStockControlId. If there is a better way to get the end result in DAX than the way I am trying, please advise!

Thanks

Re: Complex Dax Query Help

v-cazheng-msft — Tue, 16 Nov 2021 05:08:47 GMT

Hi Anonymous

You may try to use this part to finish your dax query.

// Find the rank at the 95th percentile of all ranks for each product/station

var 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

Return SUMX(table_ThresholdRank,[ThresholdRank])

EVALUATE

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!