Forum Discussion

LeboeufHarry's avatar
LeboeufHarry
Icon for Helper I rankHelper I
3 years ago

Measure driving me nuts

Hello,

I'm having a simple Grid in PowerBI Desktop, just to check some data.

It Displays a few Tables Fields and a few Measures with these definitions.

_RevenueTranCurr = SUM(Sales[RevenueCurrTrans])
_RevenueTranCurr_LY = SUM(Sales[RevenueCurrTrans_LY])

We are not working with Date Logic as the ETL with te Business Logic provides us these fields on 1 line. Nevermind.

Now I would like to have a measure that checks if there is revenue in both years. But even this measures makes the Visual go into error

_Sold_IBP_Calc = IF( [_RevenueTranCurr]<>0 ,1,0)

Error : This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.

We are displaying 6 lines of data ... Anybody has an idea what this could be

 

The definition of the _Sold_IBP_Calc would extend later, but I reduced it to this to check what could cause the problem.

Need Help

2 Replies

  • LeboeufHarry's avatar
    LeboeufHarry
    Icon for Helper I rankHelper I
    3 years ago

    I've traced the working query with DAXC studio and the failing one (after adding the measure to the visual). The line in bold is the only difference ....

     

    DEFINE
    VAR __DS0FilterTable = 
    FILTER(
    KEEPFILTERS(VALUES('Transaction_Date'[Date])),
    AND(
    'Transaction_Date'[Date] >= DATE(2023, 1, 1),
    'Transaction_Date'[Date] < DATE(2023, 6, 1)
    )
    )
     
    VAR __DS0FilterTable2 = 
    TREATAS({"600004701"}, 'Item'[ItemNameNumber])
     
    VAR __DS0FilterTable3 = 
    TREATAS({"1B09 "}, 'Tagetik_Plant'[TgkPlantCodeName])
     
    VAR __DS0FilterTable4 = 
    TREATAS(
    {"1000006720 - "},
    'Customer'[CustomerNumberName]
    )
     
    VAR __DS0FilterTable5 = 
    TREATAS({"EUR"}, 'RepCurr'[CurrencyCode])
     
    VAR __DS0Core = 
    SUMMARIZECOLUMNS(
    ROLLUPADDISSUBTOTAL(
    ROLLUPGROUP(
    'Loc_Dist'[Location Name],
    'IncoTerm'[IncoTerm Code],
    'ShipToCity'[ShipToCity_Name],
    'Tagetik_LegalEntity'[TgkLegalEntityCodeName],
    'TransCurr'[IsoCurrencyCode],
    'Item'[ItemNumber],
    'Tagetik_Plant'[TgkPlantCode],
    'Sales'[MasterKey],
    'Customer'[CustomerNumberName],
    'Sales'[SUMXKey],
    'Sales'[Invoice_Date],
    'Sales'[SoldInBothPeriods]
    ), "IsGrandTotalRowTotal"
    ),
    __DS0FilterTable,
    __DS0FilterTable2,
    __DS0FilterTable3,
    __DS0FilterTable4,
    __DS0FilterTable5,
    "SumVolumeTon", CALCULATE(SUM('Sales'[VolumeTon])),
    "Volume_Ton", 'MetaData'[Volume Ton],
    "v_RevenueTranCurr", 'MetaData'[_RevenueTranCurr],
    "v_RevenueTranCurr_LY", 'MetaData'[_RevenueTranCurr_LY],
    "v_RevenueTranCurr_PY", 'MetaData'[_RevenueTranCurr_PY],
    "v_Sold_IBP_Calc", 'MetaData'[_Sold_IBP_Calc]
    )
     
    VAR __DS0PrimaryWindowed = 
    TOPN(
    502,
    __DS0Core,
    [IsGrandTotalRowTotal],
    0,
    'Sales'[Invoice_Date],
    1,
    'Loc_Dist'[Location Name],
    1,
    'IncoTerm'[IncoTerm Code],
    1,
    'ShipToCity'[ShipToCity_Name],
    1,
    'Tagetik_LegalEntity'[TgkLegalEntityCodeName],
    1,
    'TransCurr'[IsoCurrencyCode],
    1,
    'Item'[ItemNumber],
    1,
    'Tagetik_Plant'[TgkPlantCode],
    1,
    'Sales'[MasterKey],
    1,
    'Customer'[CustomerNumberName],
    1,
    'Sales'[SUMXKey],
    1,
    'Sales'[SoldInBothPeriods],
    1
    )
     
    EVALUATE
    __DS0PrimaryWindowed
     
    ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Sales'[Invoice_Date],
    'Loc_Dist'[Location Name],
    'IncoTerm'[IncoTerm Code],
    'ShipToCity'[ShipToCity_Name],
    'Tagetik_LegalEntity'[TgkLegalEntityCodeName],
    'TransCurr'[IsoCurrencyCode],
    'Item'[ItemNumber],
    'Tagetik_Plant'[TgkPlantCode],
    'Sales'[MasterKey],
    'Customer'[CustomerNumberName],
    'Sales'[SUMXKey],
    'Sales'[SoldInBothPeriods]

     

     

    • LeboeufHarry's avatar
      LeboeufHarry
      Icon for Helper I rankHelper I
      3 years ago

      This is the error that I'm seeing in Dax Studio

       

      Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1024 MB, memory limit 1024 MB.