Forum Discussion

gianmarco's avatar
gianmarco
Helper IV
3 years ago

Nested IF statement in custom column

Dear All,

 

I'm trying to create a custom column as follows:

 

var SUPPLIERS = CALCULATE(SUM(Cashflow[Importo (+/-)]),Cashflow[Source]=0,VALUES(Cashflow[Categoria])="FORNITORI")
var PREVISIONE_FORNITORI_CURRENT = Cashflow[Source]=5 && Cashflow[Completed_month]=0 && Cashflow[Par1]=1
var CLIENTS = CALCULATE(SUM(Cashflow[Importo (+/-)]),Cashflow[Source]=0 && VALUES(Cashflow[Categoria])="CLIENTI")
var PREVISIONE_CLIENTI_CURRENT = Cashflow[Source]=5 && Cashflow[Completed_month]=0 && Cashflow[Par1]=0
var PREVISIONALE_PAST = Cashflow[Source]=5 && Cashflow[Completed_month]=1
 
RETURN
IF( PREVISIONALE_PAST, 0, IF(PREVISIONE_FORNITORI_CURRENT, Cashflow[Importo (+/-)] - (SUPPLIERS), IF(PREVISIONE_CLIENTI_CURRENT, Cashflow[Importo (+/-)] - (CLIENTS), Cashflow[Importo (+/-)])))
 
but it returns me the error: "Expression True/False does not specify a column ..."
Would you advise me how can I adjust this?

Thank you in advance
gianmarco
Need Help

4 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    This pattern is likely the issue. I see it in two places. Also, are you sure you want to create a column for this? Which table are you putting it on? A measure may be better.

     

    VALUES ( Cashflow[Categoria] ) = "FORNITORI"

     

    Try just 

     

    Cashflow[Categoria]  = "FORNITORI"

     

    Pat

  • gianmarco's avatar
    gianmarco
    Helper IV
    3 years ago

    Thank you a lot, error is gone! 

     

    Problem is: it doesn't work the following in red:

     

    RETURN
    IF( PREVISIONALE_PAST, 0, IF(PREVISIONE_FORNITORI_CURRENT, Cashflow[Importo (+/-)] - SUPPLIERS, IF(PREVISIONE_CLIENTI_CURRENT, Cashflow[Importo (+/-)] - CLIENTS, Cashflow[Importo (+/-)])))
     
    It doesn't subtract anything 😞

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi gianmarco ,

       

      Please try code as below.

       

      Column =

VAR SUPPLIERS =
    CALCULATE (
        SUM ( Cashflow[Importo (+/-)] ),
        FILTER ( Cashflow, Cashflow[Source] = 0 && Cashflow[Categoria] = "FORNITORI" )
    )
VAR PREVISIONE_FORNITORI_CURRENT = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 0
    && Cashflow[Par1] = 1
VAR CLIENTS =
    CALCULATE (
        SUM ( Cashflow[Importo (+/-)] ),
        FILTER ( Cashflow, Cashflow[Source] = 0 && Cashflow[Categoria] = "CLIENTI" )
    )
VAR PREVISIONE_CLIENTI_CURRENT = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 0
    && Cashflow[Par1] = 0
VAR PREVISIONALE_PAST = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 1
RETURN
    IF (
        PREVISIONALE_PAST,
        0,
        IF (
            PREVISIONE_FORNITORI_CURRENT,
            Cashflow[Importo (+/-)] - ( SUPPLIERS ),
            IF (
                PREVISIONE_CLIENTI_CURRENT,
                Cashflow[Importo (+/-)] - ( CLIENTS ),
                Cashflow[Importo (+/-)]
            )
        )
    )

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • gianmarco's avatar
        gianmarco
        Helper IV
        3 years ago

        It seems not working properly. Again, it doesn't subtract anything 😞