Forum Discussion

RickPowerBI's avatar
RickPowerBI
Helper I
6 years ago
Solved

Column with SWITCH statement

Hi,

 

I am trying to make a column that shows data after two “IF” scenario’s.

I’ve posted this question before but I found a problem with the solution that I got.

 

If CreditInvoice = False, the IF Column has to show the quantity as is. And not make a positive number negative as in the highlighted row.

 

Quantity

CreditInvoice

IF Column

-11346

False

-11346

23547

True

 23547

-2716713.62

False

-2716713.62

-1221689

False

-1221689

-105182

False

-105182

-67

False

-67

6

True

 6

2752

False

-2752

-2073.08

False

-2073.08

19512

True

19512

-52189

True

52189

-78499

True

78499

 

The DAX formula I have running now is:

IF Column  =

SWITCH(

     'Analytical Report'[CreditInvoice]

        = TRUE (),

    'Analytical Report'[Quantity] < 0, - ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity] > 0, ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity])

 

I’ve tried adding another switch to keep false as is, but it gives an error that you can’t change text with another value. If CreditInvoice is False, nothing needs to switch. But it keeps doing so.

 

This is what I also tried:

IF Column =

SWITCH(

   SWITCH(

     'Analytical Report'[CreditInvoice]

        = TRUE (),

    'Analytical Report'[Quantity] < 0, - ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity] > 0, ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity]),

 

    'Analytical Report'[CreditInvoice]

        = FALSE (),

    'Analytical Report'[Quantity] < 0, ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity] > 0, ( 'Analytical Report'[Quantity] ),

    'Analytical Report'[Quantity])

 

Does anyone know what is wrong?

 

Thanks in advance!

Switch()
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi RickPowerBI ,

     

     

    Please create a Calculated Column.

     

    IF1 Column =

    SWITCH(

    TRUE (),

    'Table'[Quantity] < 0 && 'Table'[CreditInvoice] = True, - ( 'Table'[Quantity]),
    'Table'[Quantity] > 0 && 'Table'[CreditInvoice] = True , 'Table'[Quantity],
    'Table'[Quantity] < 0 && 'Table'[CreditInvoice] = FALSE ,'Table'[Quantity],
    'Table'[Quantity] > 0 && 'Table'[CreditInvoice] = FALSE(), 'Table'[Quantity]
    )
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi RickPowerBI ,

     

     

    Please create a Calculated Column.

     

    IF1 Column =

    SWITCH(

    TRUE (),

    'Table'[Quantity] < 0 && 'Table'[CreditInvoice] = True, - ( 'Table'[Quantity]),
    'Table'[Quantity] > 0 && 'Table'[CreditInvoice] = True , 'Table'[Quantity],
    'Table'[Quantity] < 0 && 'Table'[CreditInvoice] = FALSE ,'Table'[Quantity],
    'Table'[Quantity] > 0 && 'Table'[CreditInvoice] = FALSE(), 'Table'[Quantity]
    )
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi RickPowerBI ,

         

        Thanks for the appreciation. 🙂 .. Happy to help you 🙂

         

         

        Regards,

        HN

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi ,

       

      I need to implement a below logic in Power BI using DAX. I  am unable to call the other tables(Purchase[Last_day],Timedim[Last_Day]) in the switch condition. 

       

      case when (( SALES.REQUEST_DT ) is null) or ( SALES.REQUEST_DT ) >= to_date('1-12-2005','MM-DD-YYYY') then 'P'
      when ( SALES.REQUEST_DT )<=( Purchase.Last_Day ) then 'PDue'
      when ( SALES.REQUEST_DT ) between to_date('08-10-2010','MM-DD-YYYY') and to_date('10-11-2010','MM-DD-YYYY') then 'BO'
      when ( SALES.REQUEST_DT ) between ( Timedim.Last_Day )+1 and to_date('01-10-2010','MM-DD-YYYY') then 'After'
      else
      to_char(to_date(Customer_Dt.FISCAL_MONTH || '/' || Customer_Dt.FISCAL_YEAR,'MM/YYYY'),'MON-YY')
      end

       

      Need help how to implement this using Power BI. Any help is highly appreciated.

       

      Thanks in advance.