Returning me all the values when i replace Blank with 0

[pbi1908]

var TABLE_TO_EXPORTED =

SUMMARIZECOLUMNS(

FT_BALANCE_BI[CUSTOMER_NAME_] ,

FT_BALANCE_BI[CURR],

"MAX_CREDIT_LIMIT", MAXX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[CUSTOMER_NAME_] = SELECTEDVALUE(FT_BALANCE_BI[CUSTOMER_NAME_]) && FT_BALANCE_BI[AGENT_BRUNO] = 1 ), FT_BALANCE_BI[CREDIT_LIMITS]),

"TOTAL BALANCE", IF(ISBLANK(SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1"  && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1  ), FT_BALANCE_BI[BALANCE] )), 0,SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1"  && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1  ), FT_BALANCE_BI[BALANCE] )),

"EXPECTED DUE BALANCE", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 && FT_BALANCE_BI[OVERDUE_FLAG] = "DUE AMOUNT" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1 &&FT_BALANCE_BI[TTYPE] = "1"  

), FT_BALANCE_BI[BALANCE] +0) ,

"DUE BALANCE", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1 &&FT_BALANCE_BI[TTYPE] = "1"  

), FT_BALANCE_BI[BALANCE] +0 ),

"0<DUE<15", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1  &&FT_BALANCE_BI[TTYPE] = "1" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1 && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT" ), FT_BALANCE_BI[15_DUE_DEFAULT_CURR] +0) ,

"15<DUE<30", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1  && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"  ), FT_BALANCE_BI[15_DUE_30_DEFAULT_CURR] +0 ) ,

"30<DUE<60", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1 && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"  ), FT_BALANCE_BI[30_DUE_60_DEFAULT_CURR] +0 ) ,

"60<DUE<120", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1   && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"), FT_BALANCE_BI[60_DUE_120_DEFAULT_CURR] +0 ) ,

"120>DUE", SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1" && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1 && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"  ), FT_BALANCE_BI[DUE_120_DEFAULT_CURR] +0) ,

"OPEN NOT SHIPPED (WITH TOLLERANCE 5%)", SUMX(FILTER(FT_SALES_BI,FT_SALES_BI[CUSTOMER_NAME] = SELECTEDVALUE(FT_BALANCE_BI[CUSTOMER_NAME_]) && FT_SALES_BI[AGENT_BRUNO] = 1 ),FT_SALES_BI[OPEN_NOT_SHIPPED] +0 ) ,

"PLANNED NOT INVOICED", ROUND(SUMX(FILTER(FT_SALES_BI,FT_SALES_BI[CUSTOMER_NAME] = SELECTEDVALUE(FT_BALANCE_BI[CUSTOMER_NAME_]) && FT_SALES_BI[AGENT_BRUNO] = 1 ),FT_SALES_BI[PLANNED_NOT_INVOICED]) +

SUMX(FILTER(FT_BALANCE_BI,FT_BALANCE_BI[AGENT_BRUNO] = 1 &&FT_BALANCE_BI[TTYPE] = "1"  && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 0  ), FT_BALANCE_BI[BALANCE] +0 ) ,2)

 )

return

TABLE_TO_EXPORTED

Hello all,

This is my DAX Query to populate a table in PBI Desktop but also in a Power Automate flow (Query Against Dataset). What i want actually are the Customer with Agent Bruno = 1, and for those i want the aging and the columns you see.  If I remove the IF statement from the Total Balance i am getting the correct results but in some cases i have blank instead of 0. This happens because maybe i have a balance but i remove it with the filter ERP_VOUCHER_FLAG = 1 that i have. 

So that's why i added the if statement because i want to see 0 instead of blank but in that case the Query is returning the Cusatomers with agent_bruno = 1 with the correct Balances aging and etc. but is returning also all the customers with everything as blank and the Total Balance 0 .

Do you know how can i fix this ?

[MFelix · Accepted solution]

Hi @pbi1908 ,

This is a question about the filter context in this case you want to have all your calculations and the add the agent_bruno filter so you can add it has a filter on your visualization or using your metric you can do it based on the filter of the table:

VAR TABLE_TO_EXPORTED =
    SUMMARIZECOLUMNS (
        FILTER ( FT_SALES_BI, FT_SALES_BI[AGENT_BRUNO] = 1 ),
        FT_BALANCE_BI[CUSTOMER_NAME_],
        FT_BALANCE_BI[CURR],
        "MAX_CREDIT_LIMIT",
            MAXX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[CUSTOMER_NAME_] = SELECTEDVALUE ( FT_BALANCE_BI[CUSTOMER_NAME_] )
                ),
                FT_BALANCE_BI[CREDIT_LIMITS]
            ),
        "TOTAL BALANCE",
            IF (
                ISBLANK (
                    SUMX (
                        FILTER (
                            FT_BALANCE_BI,
                            FT_BALANCE_BI[TTYPE] = "1"
                                && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        ),
                        FT_BALANCE_BI[BALANCE]
                    )
                ),
                0,
                SUMX (
                    FILTER (
                        FT_BALANCE_BI,
                        FT_BALANCE_BI[TTYPE] = "1"
                            && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                    ),
                    FT_BALANCE_BI[BALANCE]
                )
            ),
        "EXPECTED DUE BALANCE",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[OVERDUE_FLAG] = "DUE AMOUNT"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[TTYPE] = "1"
                ),
                FT_BALANCE_BI[BALANCE] + 0
            ),
        "DUE BALANCE",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[TTYPE] = "1"
                ),
                FT_BALANCE_BI[BALANCE] + 0
            ),
        "0<DUE<15",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[TTYPE] = "1"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                ),
                FT_BALANCE_BI[15_DUE_DEFAULT_CURR] + 0
            ),
        "15<DUE<30",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[TTYPE] = "1"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                ),
                FT_BALANCE_BI[15_DUE_30_DEFAULT_CURR] + 0
            ),
        "30<DUE<60",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[TTYPE] = "1"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                ),
                FT_BALANCE_BI[30_DUE_60_DEFAULT_CURR] + 0
            ),
        "60<DUE<120",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[TTYPE] = "1"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                ),
                FT_BALANCE_BI[60_DUE_120_DEFAULT_CURR] + 0
            ),
        "120>DUE",
            SUMX (
                FILTER (
                    FT_BALANCE_BI,
                    FT_BALANCE_BI[TTYPE] = "1"
                        && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 1
                        && FT_BALANCE_BI[OVERDUE_FLAG] = "OVERDUE AMOUNT"
                ),
                FT_BALANCE_BI[DUE_120_DEFAULT_CURR] + 0
            ),
        "OPEN NOT SHIPPED (WITH TOLLERANCE 5%)",
            SUMX (
                FILTER (
                    FT_SALES_BI,
                    FT_SALES_BI[CUSTOMER_NAME] = SELECTEDVALUE ( FT_BALANCE_BI[CUSTOMER_NAME_] )
                ),
                FT_SALES_BI[OPEN_NOT_SHIPPED] + 0
            ),
        "PLANNED NOT INVOICED",
            ROUND (
                SUMX (
                    FILTER (
                        FT_SALES_BI,
                        FT_SALES_BI[CUSTOMER_NAME] = SELECTEDVALUE ( FT_BALANCE_BI[CUSTOMER_NAME_] )
                    ),
                    FT_SALES_BI[PLANNED_NOT_INVOICED]
                )
                    + SUMX (
                        FILTER (
                            FT_BALANCE_BI,
                            FT_BALANCE_BI[TTYPE] = "1"
                                && FT_BALANCE_BI[ERP_VOUCHER_FLAG] = 0
                        ),
                        FT_BALANCE_BI[BALANCE] + 0
                    ),
                2
            )
    )
RETURN
    TABLE_TO_EXPORTED