Forum Discussion

SudhanshuD4512's avatar
SudhanshuD4512
Frequent Visitor
8 months ago
Solved

Query Exceeds

I have created a calculated column like this
A=
IF (
ISBLANK('Fact'[Inv No]),
'Fact'[Amt],
IF (
'Fact'[account_id] = "AP",
'Fact'[local_amt],
'Fact'[local_amt] * -1
)
)
After this i have created a measure on top of it Measure = SUM(A)
Now with using this i have to create other calculated column B like 
B= IF(Measure>0,Measure,0)

But when I drag this in table query exceeds resources. I am not sure why this is happening

Need Help
  • Anonymous's avatar
    Anonymous
    8 months ago

    Hi SudhanshuD4512,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Hans-Georg_Puls , kushanNa , syahmisi98 and amitchandak  for prompt and helpful responses.

     

    I’ve tried to reproduce the scenario using the M code below. Please review and adjust it according to your data source. If the issue still persists, feel free to share more details, and we’ll be happy to assist further.

     

    Thanks & Regards,

    Prasanna Kumar

11 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    8 months ago

    Hii SudhanshuD4512 

     

    Your query exceeds because you are using a measure inside a calculated column, which forces Power BI to recalculate the entire measure for every row, creating a very expensive row-by-row evaluation. Calculated columns cannot depend on measures and this pattern always causes performance issues. Instead, put all logic in a measure only, for example B = IF([Measure] > 0, [Measure], 0), and remove the second calculated column. Measures calculate at query time and will not cause “query exceeds resources,” but calculated columns with measures will.

      • amitchandak's avatar
        amitchandak
        Super User
        8 months ago

        SudhanshuD4512 , Unless this calculation has to change based on the visual row, you can have this as a row of a table, like 

        Change the calculated column like 
        A= Var _1 = IF ( ISBLANK('Fact'[Inv No]), 'Fact'[Amt],
        IF (
        'Fact'[account_id] = "AP",
        'Fact'[local_amt],
        'Fact'[local_amt] * -1
        ))
        return IF(_1>0,_1,0)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi SudhanshuD4512,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Hans-Georg_Puls , kushanNa , syahmisi98 and amitchandak  for prompt and helpful responses.

     

    I’ve tried to reproduce the scenario using the M code below. Please review and adjust it according to your data source. If the issue still persists, feel free to share more details, and we’ll be happy to assist further.

     

    Thanks & Regards,

    Prasanna Kumar

  • syahmisi98's avatar
    syahmisi98
    Advocate I
    8 months ago

    You’re using a measure inside a calculated column. That forces Power BI to evaluate the measure (which works in filter context) for every single row during refresh, and then again in visuals.

    - Move your calculated column A to Power Query

    - Create B as a measure instead of calculated column and then add the measure to your table

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos

  • kushanNa's avatar
    kushanNa
    Super User
    8 months ago

    Hi SudhanshuD4512 

     

    Is it possible to get a sample PBIX file with the issue you are having? Maybe create a small table with test data and try to recreate the issue?

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    Super User
    8 months ago

    Hi SudhanshuD4512 ,

    as rohit1991 already mentioned a calculated column is something static it cannot depend on a measure that is something dynamic. 

    I created a sample data set using same column and table names you mentioned:

    A measure that does your desired calculation should look like the following one:

    B =
    VAR _sum = SUMX(
            'Fact',
            IF(
                'Fact'[Inv No] = "",
                'Fact'[Amt],
                IF (
                    'Fact'[account_id] = "AP",
                    'Fact'[local_amt],
                    'Fact'[local_amt] * -1
                )
            )
    )
    RETURN
       IF(_sum > 0, _sum, 0)
     
    Please note that blank and empty string are not the same thing. Depending on the type of your account_id column it could be necessary to replace the condition 'Fact'[account_id] = "AP" by something different. I assumed that it is a string.
     
    If you are interested in the details of blank and empty strings and ISBLANK see for example the DAX guide 
     
    Hope that helps!
  • SudhanshuD4512's avatar
    SudhanshuD4512
    Frequent Visitor
    8 months ago

     

    Net Activity =
    IF (
    ISBLANK('Fact'[Inv No]),
    'Fact'[Amt],
    IF (
    'Fact'[account_id] = "AP",
    'Fact'[local_amt],
    'Fact'[local_amt] * -1
    )
    )
    Debit is calculated as if we sum net activity, if(net activity>0,net activity,0)
    Credit if(net activity<0,net activity,0)*-1


    • SudhanshuD4512's avatar
      SudhanshuD4512
      Frequent Visitor
      8 months ago
      Inv NumAccountIDLocal AmountAmt
      1000AP300100
      1000AP-350200
      1000AP400200

      input 

      Output below i want

      Inv NumAccountIDNet Activity DebitsCredits
      1000AP3503500




  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi SudhanshuD4512,

     

    Just following up to see if the Response provided  was helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi @SudhanshuD4512,

     

    Just following up to see if the Response provided  was helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar