Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI-BOLA
Frequent Visitor

Running out of resources for measure

Hi I have a DAX measure which works fine in a visualshowing the position of a single month but I get a message saying my visual has run out of resources when adding multiple months to the visual. Is this the most efficient formula below and are there ways I can make this same calculation less resource intensive?

Essentially I am converting a measure to a single currency. I have split the measure into multiple sections as there are multiple currencies. The exchange rate is calculated by taking the average from the present month (max month in filters). 
It's divided by 1000 at the end as numbers are to be represented in 1000s

 

up to line 55 I am converting to the currency of the exchange rate table

56-66 converts the result to USD

67-74 adds data that already exists in USD

 

 

 

 

Aging ($'000) =
(
    (
        (
            CALCULATE (
                [Aging LCY 0],
                FILTER ( 'company', 'company'[LCY_Code] = "SGD" ),
                FILTER ( customer_entries, customer_entries[Currency_Code] <> "USD" )
            )
                / CALCULATE (
                    SUM ( 'Exchange Rates'[Exchange_Rate_Amount] )
                        / COUNT ( 'Exchange Rates'[Exchange_Rate_Amount] ),
                    DATESBETWEEN (
                        'Exchange Rates'[Starting_Date],
                        EOMONTH ( MAX ( customer_entries[Posting_Date] ), -1 ) + 1,
                        EOMONTH ( MAX ( customer_entries[Posting_Date] ), 0 )
                    ),
                    FILTER ( 'Exchange Rates', 'Exchange Rates'[Currency_Code] = "SGD" )
                )
                + CALCULATE (
                    [Aging LCY 0],
                    FILTER ( 'company', 'company'[LCY_Code] = "AED" ),
                    FILTER ( customer_entries, customer_entries[Currency_Code] <> "USD" )
                )
                    / CALCULATE (
                        SUM ( 'Exchange Rates'[Exchange_Rate_Amount] )
                            / COUNT ( 'Exchange Rates'[Exchange_Rate_Amount] ),
                        DATESBETWEEN (
                            'Exchange Rates'[Starting_Date],
                            EOMONTH ( MAX ( customer_entries[Posting_Date] ), -1 ) + 1,
                            EOMONTH ( MAX ( customer_entries[Posting_Date] ), 0 )
                        ),
                        FILTER ( 'Exchange Rates', 'Exchange Rates'[Currency_Code] = "AED" )
                    )
                + CALCULATE (
                    [Aging LCY 0],
                    FILTER ( 'company', 'company'[LCY_Code] = "INR" ),
                    FILTER ( customer_entries, customer_entries[Currency_Code] <> "USD" )
                )
                    / CALCULATE (
                        SUM ( 'Exchange Rates'[Exchange_Rate_Amount] )
                            / COUNT ( 'Exchange Rates'[Exchange_Rate_Amount] ),
                        DATESBETWEEN (
                            'Exchange Rates'[Starting_Date],
                            EOMONTH ( MAX ( customer_entries[Posting_Date] ), -1 ) + 1,
                            EOMONTH ( MAX ( customer_entries[Posting_Date] ), 0 )
                        ),
                        FILTER ( 'Exchange Rates', 'Exchange Rates'[Currency_Code] = "INR" )
                    )
                + CALCULATE (
                    [Aging LCY 0],
                    FILTER ( 'company', 'company'[LCY_Code] = "GBP" ),
                    FILTER ( customer_entries, customer_entries[Currency_Code] <> "USD" )
                )
        )
            * CALCULATE (
                SUM ( 'Exchange Rates'[Exchange_Rate_Amount] )
                    / COUNT ( 'Exchange Rates'[Exchange_Rate_Amount] ),
                DATESBETWEEN (
                    'Exchange Rates'[Starting_Date],
                    EOMONTH ( MAX ( customer_entries[Posting_Date] ), -1 ) + 1,
                    EOMONTH ( MAX ( customer_entries[Posting_Date] ), 0 )
                ),
                FILTER ( 'Exchange Rates', 'Exchange Rates'[Currency_Code] = "USD" )
            )
    )
        + (
            CALCULATE ( [Aging LCY 0], FILTER ( 'company', 'company'[LCY_Code] = "USD" ) )
                + CALCULATE (
                    [Aging 0],
                    FILTER ( customer_entries, customer_entries[Currency_Code] = "USD" ),
                    FILTER ( 'company', 'company'[LCY_Code] <> "USD" )
                )
        )
) / 1000

 

 

 

 

1 ACCEPTED SOLUTION
swikritee_p
Resolver II
Resolver II

Check if this can help

 

Aging ($000) = 
VAR CurrentEOMonth = EOMONTH(MAX(customer_entries[Posting_Date]), 0)
VAR PrevEOMonth = EOMonth(MAX(customer_entries[Posting_Date]), -1) + 1
VAR USDExchangeRate = 
    CALCULATE(
        SUM('Exchange Rates'[Exchange_Rate_Amount]) / COUNT('Exchange Rates'[Exchange_Rate_Amount]),
        'Exchange Rates'[Starting_Date] >= PrevEOMonth,
        'Exchange Rates'[Starting_Date] <= CurrentEOMonth,
        'Exchange Rates'[Currency_Code] = "USD"
    )
VAR ConvertedToUSD =
    SUMX(
        VALUES('company'[LCY_Code]),
        VAR LCY_Code = 'company'[LCY_Code]
        VAR ExchangeRate = 
            CALCULATE(
                SUM('Exchange Rates'[Exchange_Rate_Amount]) / COUNT('Exchange Rates'[Exchange_Rate_Amount]),
                'Exchange Rates'[Starting_Date] >= PrevEOMonth,
                'Exchange Rates'[Starting_Date] <= CurrentEOMonth,
                'Exchange Rates'[Currency_Code] = LCY_Code
            )
        VAR AgingLCY = CALCULATE([Aging LCY 0], 'company'[LCY_Code] = LCY_Code)
        RETURN IF(LCY_Code <> "USD", AgingLCY / ExchangeRate, BLANK())
    ) * USDExchangeRate
VAR DirectUSDAmounts = 
    CALCULATE([Aging LCY 0], 'company'[LCY_Code] = "USD") +
    CALCULATE([Aging 0], customer_entries[Currency_Code] = "USD", 'company'[LCY_Code] <> "USD")
RETURN (ConvertedToUSD + DirectUSDAmounts) / 1000

View solution in original post

2 REPLIES 2
swikritee_p
Resolver II
Resolver II

Check if this can help

 

Aging ($000) = 
VAR CurrentEOMonth = EOMONTH(MAX(customer_entries[Posting_Date]), 0)
VAR PrevEOMonth = EOMonth(MAX(customer_entries[Posting_Date]), -1) + 1
VAR USDExchangeRate = 
    CALCULATE(
        SUM('Exchange Rates'[Exchange_Rate_Amount]) / COUNT('Exchange Rates'[Exchange_Rate_Amount]),
        'Exchange Rates'[Starting_Date] >= PrevEOMonth,
        'Exchange Rates'[Starting_Date] <= CurrentEOMonth,
        'Exchange Rates'[Currency_Code] = "USD"
    )
VAR ConvertedToUSD =
    SUMX(
        VALUES('company'[LCY_Code]),
        VAR LCY_Code = 'company'[LCY_Code]
        VAR ExchangeRate = 
            CALCULATE(
                SUM('Exchange Rates'[Exchange_Rate_Amount]) / COUNT('Exchange Rates'[Exchange_Rate_Amount]),
                'Exchange Rates'[Starting_Date] >= PrevEOMonth,
                'Exchange Rates'[Starting_Date] <= CurrentEOMonth,
                'Exchange Rates'[Currency_Code] = LCY_Code
            )
        VAR AgingLCY = CALCULATE([Aging LCY 0], 'company'[LCY_Code] = LCY_Code)
        RETURN IF(LCY_Code <> "USD", AgingLCY / ExchangeRate, BLANK())
    ) * USDExchangeRate
VAR DirectUSDAmounts = 
    CALCULATE([Aging LCY 0], 'company'[LCY_Code] = "USD") +
    CALCULATE([Aging 0], customer_entries[Currency_Code] = "USD", 'company'[LCY_Code] <> "USD")
RETURN (ConvertedToUSD + DirectUSDAmounts) / 1000

Thanks! Had to make a minor change as a filter was missing in ConvertedToUSD, it's working in the service now. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.