Forum Discussion

jaryszek's avatar
jaryszek
Super User
10 months ago
Solved

Dax for total row optimization

hi Guys,

this is my DAX:

EffectivePrice Percentage = 
IF(ISINSCOPE(Fct_EA_AmortizedCosts[Discountability]) || ISINSCOPE(Fct_EA_AmortizedCosts[DiscountPlanName]) || ISINSCOPE(Fct_EA_AmortizedCosts[SubscriptionName]) || ISINSCOPE(Dim_EA_AmortizedCosts_Resources[ResourceType]),
    DIVIDE(
    SUMX(
        Fct_EA_AmortizedCosts,
        ('Fct_EA_AmortizedCosts'[EffectivePrice]) * ('Fct_EA_AmortizedCosts'[WeightInBillingCurrency])
    ),
    SUM('Fct_EA_AmortizedCosts'[WeightInBillingCurrency])
    ) / 100, 
    BLANK()
)


as you can see I am adding ISINSCOPE all the time when new page with new columns in matrix arrived in order to not show total row. 

Is there a possibility to optimize this DAX to be more generic?

Best,
Jacek

Cost
Row
total
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    10 months ago

    Thankyou, bhanu_gautam, for your response.

    Hi jaryszek,

    We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

    Based on my understanding, DAX currently does not provide a built-in method to automatically detect which fields are used in a Matrix visual or to determine whether a cell represents a total or a subtotal. Functions such as ISINSCOPE(), HASONEVALUE(), and HASONEFILTER() operate on a per column basis and therefore require explicit column references. This behaviour is by design and does not indicate a product defect.

    DAX cannot generically detect totals without specifying columns. The helper measure pattern below is a best practice approach for long term maintainability and performance.

    1. To simplify maintenance and optimise your measure, you can centralise all your ISINSCOPE() checks in a single helper measure or variable instead of repeating them:

    -- Helper measure (optional)
    IsRowContext =
    OR(
    ISINSCOPE(Fct_EA_AmortizedCosts[Discountability]),
    ISINSCOPE(Fct_EA_AmortizedCosts[DiscountPlanName]),
    ISINSCOPE(Fct_EA_AmortizedCosts[SubscriptionName]),
    ISINSCOPE(Dim_EA_AmortizedCosts_Resources[ResourceType])
    )

    -- Final measure
    EffectivePrice Percentage =
    VAR Numerator =
    SUMX(
    Fct_EA_AmortizedCosts,
    Fct_EA_AmortizedCosts[EffectivePrice] * Fct_EA_AmortizedCosts[WeightInBillingCurrency]
    )
    VAR Denominator = SUM(Fct_EA_AmortizedCosts[WeightInBillingCurrency])
    VAR WeightedAvg = DIVIDE(Numerator, Denominator) / 100
    RETURN
    IF([IsRowContext], WeightedAvg, BLANK())

    This approach ensures that you maintain only a single location (IsRowContext) if you add new columns later.

    Alternatively, to hide totals visually, go to the Format pane, select Subtotals, and turn off Row or Column totals.

    We hope that the information provided will help to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

    Thank you.

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    10 months ago

    jaryszek If you want to show the measure only when a single value is selected for [Discountability], [DiscountPlanName], [SubscriptionName], or [ResourceType], you can do:

     

    DAX
    EffectivePrice Percentage =
    IF(
    HASONEVALUE(Fct_EA_AmortizedCosts[Discountability])
    || HASONEVALUE(Fct_EA_AmortizedCosts[DiscountPlanName])
    || HASONEVALUE(Fct_EA_AmortizedCosts[SubscriptionName])
    || HASONEVALUE(Dim_EA_AmortizedCosts_Resources[ResourceType]),
    DIVIDE(
    SUMX(
    Fct_EA_AmortizedCosts,
    Fct_EA_AmortizedCosts[EffectivePrice] * Fct_EA_AmortizedCosts[WeightInBillingCurrency]
    ),
    SUM(Fct_EA_AmortizedCosts[WeightInBillingCurrency])
    ) / 100,
    BLANK()
    )

    • jaryszek's avatar
      jaryszek
      Super User
      10 months ago

      thanks, 

      but still i need to provide field names, it is not generic. 

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    10 months ago

    Thankyou, bhanu_gautam, for your response.

    Hi jaryszek,

    We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

    Based on my understanding, DAX currently does not provide a built-in method to automatically detect which fields are used in a Matrix visual or to determine whether a cell represents a total or a subtotal. Functions such as ISINSCOPE(), HASONEVALUE(), and HASONEFILTER() operate on a per column basis and therefore require explicit column references. This behaviour is by design and does not indicate a product defect.

    DAX cannot generically detect totals without specifying columns. The helper measure pattern below is a best practice approach for long term maintainability and performance.

    1. To simplify maintenance and optimise your measure, you can centralise all your ISINSCOPE() checks in a single helper measure or variable instead of repeating them:

    -- Helper measure (optional)
    IsRowContext =
    OR(
    ISINSCOPE(Fct_EA_AmortizedCosts[Discountability]),
    ISINSCOPE(Fct_EA_AmortizedCosts[DiscountPlanName]),
    ISINSCOPE(Fct_EA_AmortizedCosts[SubscriptionName]),
    ISINSCOPE(Dim_EA_AmortizedCosts_Resources[ResourceType])
    )

    -- Final measure
    EffectivePrice Percentage =
    VAR Numerator =
    SUMX(
    Fct_EA_AmortizedCosts,
    Fct_EA_AmortizedCosts[EffectivePrice] * Fct_EA_AmortizedCosts[WeightInBillingCurrency]
    )
    VAR Denominator = SUM(Fct_EA_AmortizedCosts[WeightInBillingCurrency])
    VAR WeightedAvg = DIVIDE(Numerator, Denominator) / 100
    RETURN
    IF([IsRowContext], WeightedAvg, BLANK())

    This approach ensures that you maintain only a single location (IsRowContext) if you add new columns later.

    Alternatively, to hide totals visually, go to the Format pane, select Subtotals, and turn off Row or Column totals.

    We hope that the information provided will help to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

    Thank you.