How to correct my Multiple IF Statements with AND

[msoriano07]

Hello Everyone,

I am creating a report in power BI and trying to create mutiple IF Statements with AND in a column, just not sure how to correct this and now I am lost. As per screenshot below, it is saying the Function SUM is not allowed in Direct Query.

Please help!

Status = IF([ACCOUNT_NAME]="","",
IF(AND([ACCOUNT_TYPE]="IC Account",[CLOSING_BALANCE_AMOUNT]>0),"Receivable",
IF(AND([ACCOUNT_TYPE]="IC Account",[CLOSING_BALANCE_AMOUNT]<0),"Payable",
IF(AND([ACCOUNT_TYPE]="IC Account",[CLOSING_BALANCE_AMOUNT]=0),"-",
IF(AND([ACCOUNT_TYPE]="Bank Accountl",[Excess Cash / Funding Needed]>0),"Excess",
IF(AND([ACCOUNT_TYPE]="Bank Account",[Excess Cash / Funding Needed]<0),"Funding needed",
IF(AND([ACCOUNT_TYPE]="Bank Account",[Excess Cash / Funding Needed]=0),"-","")))))))

Thanks.

[v-ssriganesh]

Hi @msoriano07,
Thank you for posting your query in the Microsoft Fabric Community Forum, could you please provide sample data that clearly illustrates the issue you're experiencing?

• Include a small dataset that fully captures the issue (preferably in table format or as a downloadable file, not just a screenshot).
• Avoid including any sensitive or unrelated information.
• Also share the expected outcome based on the sample data you provide.

Need help preparing or uploading sample data? You can refer to this helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will help us reproduce your scenario accurately and provide a precise solution.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @msoriano07,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[DataNinja777]

Hi @msoriano07 ,

The error "Function SUM is not allowed in Direct Query" is happening because your formula for the Status calculated column refers to [Excess Cash / Funding Needed], which is likely a measure that uses an aggregation like SUM. In DirectQuery mode, you simply can't use measures or aggregation functions inside a calculated column. This is because a calculated column is computed one row at a time during data refresh and doesn't have the overall "context" needed to perform an aggregation for each individual row.

The correct solution is to move your logic out of a calculated column and into a measure. Measures are designed for this exact scenario; they are calculated on the fly when you use them in a visual and can easily reference other measures. It's also a great opportunity to simplify your formula by using the SWITCH function instead of many nested IFs. It's much cleaner and more efficient. In Power BI, click on New Measure and paste the following DAX code.

Status Measure =
-- The SWITCH(TRUE(), ...) pattern allows checking multiple logical conditions in order.
SWITCH(
    TRUE(),
    -- First, check if the account name is blank.
    SELECTEDVALUE('YourTable'[ACCOUNT_NAME]) = "", BLANK(),

    -- Condition for "Receivable"
    SELECTEDVALUE('YourTable'[ACCOUNT_TYPE]) = "IC Account" && [CLOSING_BALANCE_AMOUNT] > 0, "Receivable",

    -- Condition for "Payable"
    SELECTEDVALUE('YourTable'[ACCOUNT_TYPE]) = "IC Account" && [CLOSING_BALANCE_AMOUNT] < 0, "Payable",

    -- Condition for "Excess"
    -- This now works because [Excess Cash / Funding Needed] is evaluated within a measure.
    SELECTEDVALUE('YourTable'[ACCOUNT_TYPE]) = "Bank Account" && [Excess Cash / Funding Needed] > 0, "Excess",

    -- Condition for "Funding needed"
    SELECTEDVALUE('YourTable'[ACCOUNT_TYPE]) = "Bank Account" && [Excess Cash / Funding Needed] < 0, "Funding needed",

    -- Default condition for all zero balances or other cases
    "-"
)

Remember to replace 'YourTable' with the actual name of your table. I used SELECTEDVALUE because measures operate on the aggregated context of your visual. When you use this new [Status Measure] in a table or chart, it will correctly evaluate the conditions, including your other measure, and display the right status without the DirectQuery error. The SWITCH function makes the logic much easier to read by listing each condition and its result in a clear sequence, avoiding the confusing mess of nested parentheses.

Best regards,

[msoriano07]

Hello Thank you so much for answering.

So far, your answer is the closest with less error. I just can't understand why the closing balancecannot be determined. 

 

[wardy912]

Hi @msoriano07 

 In direct query, functions like SUM, AVERAGE etc. are restricted to measures.

Your corrected calculated column is as follows:

Status = 
IF(
    [ACCOUNT_NAME] = "", 
    "", 
    SWITCH(
        TRUE(),
        [ACCOUNT_TYPE] = "IC Account" && [CLOSING_BALANCE_AMOUNT] > 0, "Receivable",
        [ACCOUNT_TYPE] = "IC Account" && [CLOSING_BALANCE_AMOUNT] < 0, "Payable",
        [ACCOUNT_TYPE] = "IC Account" && [CLOSING_BALANCE_AMOUNT] = 0, "-",
        [ACCOUNT_TYPE] = "Bank Account" && [Excess Cash / Funding Needed] > 0, "Excess",
        [ACCOUNT_TYPE] = "Bank Account" && [Excess Cash / Funding Needed] < 0, "Funding needed",
        [ACCOUNT_TYPE] = "Bank Account" && [Excess Cash / Funding Needed] = 0, "-",
        ""
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[MohamedFowzan1]

Hi @msoriano07 

Yes Direct query has some limitations

You could create this as a measure if you would like to use sums:

Status =
IF(
    SELECTEDVALUE([ACCOUNT_NAME]) = "", BLANK(),
    IF(
        AND([ACCOUNT_TYPE] = "IC Account", [CLOSING_BALANCE_AMOUNT] > 0), "Receivable",
        IF(
            AND([ACCOUNT_TYPE] = "IC Account", [CLOSING_BALANCE_AMOUNT] < 0), "Payable",
            IF(
                AND([ACCOUNT_TYPE] = "IC Account", [CLOSING_BALANCE_AMOUNT] = 0), "-",
                IF(
                    AND([ACCOUNT_TYPE] = "Bank Account", [Excess Cash / Funding Needed] > 0), "Excess",
                    IF(
                        AND([ACCOUNT_TYPE] = "Bank Account", [Excess Cash / Funding Needed] < 0), "Funding needed",
                        IF(
                            AND([ACCOUNT_TYPE] = "Bank Account", [Excess Cash / Funding Needed] = 0), "-",
                            BLANK()
                        )
                    )
                )
            )
        )
    )
)

else you would have to Use Only Columns (Not Measures) within in the Calculated Columns e.g [Excess Cash / Funding Needed] would have to also be a column instead of measure

[Greg_Deckler]

@msoriano07 Try this:

Status = 
  SWITCH( TRUE(),
    [ACCOUNT_NAME]="","",
    [ACCOUNT_TYPE]="IC Account" && [CLOSING_BALANCE_AMOUNT]>0,"Receivable",
    [ACCOUNT_TYPE]="IC Account" && [CLOSING_BALANCE_AMOUNT]<0,"Payable",
    [ACCOUNT_TYPE]="IC Account" && [CLOSING_BALANCE_AMOUNT]=0,"-",
    [ACCOUNT_TYPE]="Bank Accountl" && [Excess Cash / Funding Needed]>0,"Excess",
    [ACCOUNT_TYPE]="Bank Account" && [Excess Cash / Funding Needed]<0),"Funding needed",
    [ACCOUNT_TYPE]="Bank Account" && [Excess Cash / Funding Needed]=0),"-",
    ""
  )

[msoriano07]

Thanks a lot for answering but I am still having issue ☹️