Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pbi1908
Helper III
Helper III

Returning me all the values when i replace Blank with 0

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 ?
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.