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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RickPowerBI
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

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]
)
 
 
1.jpg
 

Regards,
Harsh Nathani

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

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

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]
)
 
 
1.jpg
 

Regards,
Harsh Nathani

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

Anonymous
Not applicable

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.

You're an absulute legend! Thank you very much.

Hi @RickPowerBI ,

 

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

 

 

Regards,

HN

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors