Could someone help me to understand the below dax query 1

chiru5262 — Tue, 09 Jul 2024 12:46:11 GMT

Ash_repeated failure severity col classification =

var currentAssetClass = FACT_API_SEVERITY[asset class]

var currentDiameter = FACT_API_SEVERITY[pipeSizeGroup]

var currentClassification = FACT_API_SEVERITY[Classification]

var ComparisonPeriodBMTBL =

SUMMARIZE(FACT_API_SEVERITY, FACT_API_SEVERITY[asset class], FACT_API_SEVERITY[ASSETNUM], FACT_API_SEVERITY[pipeSizeGroup],

"PerChange", sumx(filter(FACT_API_SEVERITY, FACT_API_SEVERITY[COMPARISON_PERIOD_BM]>0 && FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD]=1 && FACT_API_SEVERITY[asset class]=currentAssetClass && FACT_API_SEVERITY[pipeSizeGroup]=currentDiameter && isblank(FACT_API_Severity[RF preSeverity]) && FACT_API_SEVERITY[LocStatus]="OPERATING" && FACT_API_SEVERITY[assetStatus]="EXISTING" && FACT_API_SEVERITY[isFacilityAsset]=0), FACT_API_SEVERITY[COMPARISON_PERIOD_BM]))

var ComparisonPeriodBMTBL_Nonlinear =

SUMMARIZE(FACT_API_SEVERITY, FACT_API_SEVERITY[Classification], FACT_API_SEVERITY[ASSETNUM], FACT_API_SEVERITY[asset class],

"PerChange", sumx(filter(FACT_API_SEVERITY, FACT_API_SEVERITY[COMPARISON_PERIOD_BM]>0 && FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD]=1 && FACT_API_SEVERITY[Classification]=currentClassification && FACT_API_SEVERITY[asset class]=currentAssetClass && isblank(FACT_API_Severity[RF preSeverity]) && FACT_API_SEVERITY[LocStatus]="OPERATING" && FACT_API_SEVERITY[assetStatus]="EXISTING" && FACT_API_SEVERITY[isFacilityAsset]=0), FACT_API_SEVERITY[COMPARISON_PERIOD_BM]))

var maxPerChange = if(FACT_API_Severity[CLASSTYPE]="LINEAR", maxx(ComparisonPeriodBMTBL, [PerChange]), maxx(ComparisonPeriodBMTBL_Nonlinear, [PerChange]))

var minPerChange = 0 //if(FACT_API_Severity[CLASSTYPE]="LINEAR", minx(ComparisonPeriodBMTBL, [PerChange]) , minx(ComparisonPeriodBMTBL_Nonlinear, [PerChange]))

var currentValue =

switch(true(),

FACT_API_SEVERITY[isFacilityAsset]=1 || FACT_API_SEVERITY[assetStatus] <> "EXISTING" || FACT_API_SEVERITY[LocStatus] <> "OPERATING", blank(),

(FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD])=0, blank(),

(FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD])=1, FACT_API_SEVERITY[COMPARISON_PERIOD_BM],

blank())

var RepeatedFailureSeverity =

switch(true(),

isblank(currentValue), blank(),

not isblank(FACT_API_Severity[RF preSeverity]), FACT_API_Severity[RF preSeverity],

(FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD])=0, blank(),

(FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD])=1, if(maxPerChange-minPerChange=0, 1, ((4*(currentValue-minPerChange))/(maxPerChange-minPerChange)+1)),

blank())

return ROUND(RepeatedFailureSeverity, 0)

Re: Could someone help me to understand the below dax query 1

aduguid — Tue, 09 Jul 2024 13:10:36 GMT

I've refactored the DAX and added comments.

Here's a good online tool for formatting DAX. https://www.daxformatter.com

Ash_repeated_failure_severity_col_classification = // Variables to store the current asset class, diameter, and classification from the context VAR currentAssetClass = FACT_API_SEVERITY[asset class] VAR currentDiameter = FACT_API_SEVERITY[pipeSizeGroup] VAR currentClassification = FACT_API_SEVERITY[Classification] // Summarize table for linear assets with relevant filters and calculations VAR ComparisonPeriodBMTBL = SUMMARIZE( FACT_API_SEVERITY, FACT_API_SEVERITY[asset class], FACT_API_SEVERITY[ASSETNUM], FACT_API_SEVERITY[pipeSizeGroup], "PerChange", SUMX( FILTER( FACT_API_SEVERITY, FACT_API_SEVERITY[COMPARISON_PERIOD_BM] > 0 && FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 1 && FACT_API_SEVERITY[asset class] = currentAssetClass && FACT_API_SEVERITY[pipeSizeGroup] = currentDiameter && ISBLANK(FACT_API_SEVERITY[RF preSeverity]) && FACT_API_SEVERITY[LocStatus] = "OPERATING" && FACT_API_SEVERITY[assetStatus] = "EXISTING" && FACT_API_SEVERITY[isFacilityAsset] = 0 ), FACT_API_SEVERITY[COMPARISON_PERIOD_BM] ) ) // Summarize table for non-linear assets with relevant filters and calculations VAR ComparisonPeriodBMTBL_Nonlinear = SUMMARIZE( FACT_API_SEVERITY, FACT_API_SEVERITY[Classification], FACT_API_SEVERITY[ASSETNUM], FACT_API_SEVERITY[asset class], "PerChange", SUMX( FILTER( FACT_API_SEVERITY, FACT_API_SEVERITY[COMPARISON_PERIOD_BM] > 0 && FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 1 && FACT_API_SEVERITY[Classification] = currentClassification && FACT_API_SEVERITY[asset class] = currentAssetClass && ISBLANK(FACT_API_SEVERITY[RF preSeverity]) && FACT_API_SEVERITY[LocStatus] = "OPERATING" && FACT_API_SEVERITY[assetStatus] = "EXISTING" && FACT_API_SEVERITY[isFacilityAsset] = 0 ), FACT_API_SEVERITY[COMPARISON_PERIOD_BM] ) ) // Calculate the maximum PerChange value based on asset type (Linear/Non-Linear) VAR maxPerChange = IF( FACT_API_SEVERITY[CLASSTYPE] = "LINEAR", MAXX(ComparisonPeriodBMTBL, [PerChange]), MAXX(ComparisonPeriodBMTBL_Nonlinear, [PerChange]) ) // Set the minimum PerChange value to 0 (adjust as needed) // VAR minPerChange = // IF( // FACT_API_SEVERITY[CLASSTYPE] = "LINEAR", // MINX(ComparisonPeriodBMTBL, [PerChange]), // MINX(ComparisonPeriodBMTBL_Nonlinear, [PerChange]) // ) VAR minPerChange = 0 // Determine the current value based on various conditions VAR currentValue = SWITCH( TRUE(), FACT_API_SEVERITY[isFacilityAsset] = 1 || FACT_API_SEVERITY[assetStatus] <> "EXISTING" || FACT_API_SEVERITY[LocStatus] <> "OPERATING", BLANK(), FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 0, BLANK(), FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 1, FACT_API_SEVERITY[COMPARISON_PERIOD_BM], BLANK() ) // Calculate the Repeated Failure Severity based on conditions VAR RepeatedFailureSeverity = SWITCH( TRUE(), ISBLANK(currentValue), BLANK(), NOT ISBLANK(FACT_API_SEVERITY[RF preSeverity]), FACT_API_SEVERITY[RF preSeverity], FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 0, BLANK(), FACT_API_SEVERITY[NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD] = 1, IF( maxPerChange - minPerChange = 0, 1, ((4 * (currentValue - minPerChange)) / (maxPerChange - minPerChange) + 1) ), BLANK() ) // Return the rounded value of Repeated Failure Severity RETURN ROUND(RepeatedFailureSeverity, 0)

topic Could someone help me to understand the below dax query 1 in DAX Commands and Tips

Could someone help me to understand the below dax query 1

Re: Could someone help me to understand the below dax query 1