Could someone help me to understand the below dax query 1

[chiru5262]

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)

[aduguid · Accepted solution]

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)