Forum Discussion

pezakas's avatar
pezakas
Icon for Helper I rankHelper I
2 years ago
Solved

"Visual has exceeded the available resources" - Measure optimization

Hello community,

 

i have an issue in 2 visuals where i use measures to count while user choose date within a date range slicer.
We use a Premium account / PowerBI workspace.
I am trying to find alternative ways to make these measures work the same way more efficiently. 

1st scenario:

Column chart showing policies with premium decreases within a date range:

 

# Policies per Date Range (with Last_premium_decrease_date)) =
VAR SelectedStartDate = CALCULATE(MIN('DIM_CALENDAR_POLICY'[Date]), USERELATIONSHIP(DIM_CALENDAR_POLICY[Date],DIM_POLICY[Last_premium_decrease_date]))
VAR SelectedEndDate = CALCULATE(MAX('DIM_CALENDAR_POLICY'[Date]), USERELATIONSHIP(DIM_CALENDAR_POLICY[Date],DIM_POLICY[Last_premium_decrease_date]))
RETURN
CALCULATE(
    DISTINCTCOUNT(DIM_POLICY[Policy_ID]),
    FILTER(
         DIM_POLICY,       
        'DIM_POLICY'[Last_premium_decrease_date] <= SelectedEndDate &&
        'DIM_POLICY'[ETL_deletion_flag] <> True
    ),
    FILTER(
         ALL(DIM_POLICY),
        'DIM_POLICY'[Last_premium_decrease_date] = CALCULATE(MAX('DIM_POLICY'[Last_premium_decrease_date]), ALLEXCEPT(DIM_POLICY,DIM_POLICY[Policy_ID]),'DIM_POLICY'[Last_premium_decrease_date] <= SelectedEndDate)
   ), USERELATIONSHIP(DIM_CALENDAR_POLICY[Date],DIM_POLICY[Last_premium_decrease_date])
)+0


2nd scenario

Linechart showing number of customers with specific characteristics withi a date range:

# Customers per Date Range =
VAR SelectedStartDate = MIN('DIM_CALENDAR_PARTNER'[Date])
VAR SelectedEndDate = MAX('DIM_CALENDAR_PARTNER'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT(DIM_PARTNER[Partner_ID]),
    USERELATIONSHIP(DIM_CALENDAR_PARTNER[Date],DIM_PARTNER[Effective_date]),
    FILTER(
         DIM_PARTNER,  
 //       'DIM_PARTNER'[Effective_date] >= SelectedStartDate &&    
        'DIM_PARTNER'[Effective_date] <= SelectedEndDate &&
        'DIM_PARTNER'[ETL_deletion_flag] <> True //&&
        //'DIM_POLICY'[Policy_status]
    ),
    FILTER(REL_POLICY_X_PARTNER, REL_POLICY_X_PARTNER[Role] = "Owner 1 - OW1"),
    FILTER(DIM_POLICY, DIM_POLICY[Policy_status] in {"Inforce", "Inforce - first year", "Renewal"}),
    FILTER(
         ALL(DIM_PARTNER),
        'DIM_PARTNER'[Effective_date] = CALCULATE(MAX('DIM_PARTNER'[Effective_date]), ALLEXCEPT(DIM_PARTNER, DIM_PARTNER[Partner_ID]),'DIM_PARTNER'[Effective_date] <= SelectedEndDate)
   )
)

Thanks in advance
Need Help
Show and Tell
Tips and Tricks
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    Hi pezakas 

    Can you please try below code

    VAR SelectedStartDate = MIN('DIM_CALENDAR_PARTNER'[Date])
VAR SelectedEndDate = MAX('DIM_CALENDAR_PARTNER'[Date])

RETURN
CALCULATE(
    DISTINCTCOUNT(DIM_PARTNER[Partner_ID]),
    USERELATIONSHIP(DIM_CALENDAR_PARTNER[Date], DIM_PARTNER[Effective_date]),
    DIM_PARTNER[Effective_date] <= SelectedEndDate,
    DIM_PARTNER[ETL_deletion_flag] <> True,
    REL_POLICY_X_PARTNER[Role] = "Owner 1 - OW1",
    DIM_POLICY[Policy_status] IN {"Inforce", "Inforce - first year", "Renewal"},
    FILTER(
        ALL(DIM_PARTNER),
        CALCULATE(
            MAX(DIM_PARTNER[Effective_date]),
            ALLEXCEPT(DIM_PARTNER, DIM_PARTNER[Partner_ID]),
            DIM_PARTNER[Effective_date] <= SelectedEndDate
        ) = DIM_PARTNER[Effective_date]
    )
)

     
    If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

    Thanks
    Pijush

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi pezakas ,

     

    Your measure doesn't seem particularly complex to compute, please consider starting with optimizing the semantic model.And you can refer to:how to fix visual has exceeded the available resource. - Microsoft Q&A

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi pezakas 

    Can you please try below code

    VAR SelectedStartDate = MIN('DIM_CALENDAR_PARTNER'[Date])
VAR SelectedEndDate = MAX('DIM_CALENDAR_PARTNER'[Date])

RETURN
CALCULATE(
    DISTINCTCOUNT(DIM_PARTNER[Partner_ID]),
    USERELATIONSHIP(DIM_CALENDAR_PARTNER[Date], DIM_PARTNER[Effective_date]),
    DIM_PARTNER[Effective_date] <= SelectedEndDate,
    DIM_PARTNER[ETL_deletion_flag] <> True,
    REL_POLICY_X_PARTNER[Role] = "Owner 1 - OW1",
    DIM_POLICY[Policy_status] IN {"Inforce", "Inforce - first year", "Renewal"},
    FILTER(
        ALL(DIM_PARTNER),
        CALCULATE(
            MAX(DIM_PARTNER[Effective_date]),
            ALLEXCEPT(DIM_PARTNER, DIM_PARTNER[Partner_ID]),
            DIM_PARTNER[Effective_date] <= SelectedEndDate
        ) = DIM_PARTNER[Effective_date]
    )
)

     
    If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

    Thanks
    Pijush