Forum Discussion

SudhanshuD4512's avatar
SudhanshuD4512
Frequent Visitor
9 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 c...
Need Help
  • Anonymous's avatar
    Anonymous
    9 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

Hans-Georg_Puls's avatar
Hans-Georg_Puls
Icon for Super User rankSuper User
9 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!