topic Re: Column with SWITCH statement in DAX Commands and Tips

Column with SWITCH statement

RickPowerBI — Tue, 26 May 2020 11:08:54 GMT

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(

'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!

Re: Column with SWITCH statement

Anonymous — Tue, 26 May 2020 11:40:01 GMT

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)

Re: Column with SWITCH statement

RickPowerBI — Tue, 26 May 2020 11:45:07 GMT

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

Re: Column with SWITCH statement

Anonymous — Tue, 26 May 2020 11:50:56 GMT

Hi RickPowerBI ,

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

Regards,

Re: Column with SWITCH statement

Anonymous — Thu, 20 Aug 2020 19:03:36 GMT

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.