Slicer to switch between 2 columns

[Anonymous]

Hi,

I want to create a slicer to switch between 2 columms, which are displayed in the visuals. One column contains the invoice amount including taxes (21%), the other column contains the same invoices, but without taxes ((invoice amount / 121) * 100).

I have different visuals which must be updated when I filter between the two options (incl. tax, excl. tax).

Does anyone have an idea how to accomplish this? It seems rather simple, but I can't figure it out.

[parry2k · Accepted solution]

@Anonymous create another table with these two options and this table be not have relationship with anything, put column from this table as a slicer and use following dax to switch between values.

Change table name/column name in following dax as per your data model. Now you can use following "Amount" measure in your visuals and value will change based on option selected on the slicer.

Hope it is helpful

Amount = 
VAR __selectedSlicer = SELECTEDVALUE( NewTable[Slicer] )
RETURN
IF( __selectedSlicer = "Incl. Tax", SUM( Table[InclAmount] ), SUM( Table[ExclAmount] ) )

[Anonymous]

Hello,

I am trying to replicate the same logic for my dashboard, however, it continuously defaults to the IF statement's 'fail' option.

I'm trying to do this for currency conversion, so I have a new table "Report Currency" with one column named "To_Currency" with 3 options, USD/EUR/CNY.

Additionally, I first created 3 LOOKUPVALUE statements for the report.  The report has sales data coming through in various currencies.  So I also have a Currency Exchange table with 'report currencies' in the rows and 'To_Currency" optiosn as the columns with the rates filled out.  These are confirmed to be working (named [USD Lookup] / [EUR Lookup] / [CNY Lookup]).

Again, no matter the currency selected in the slicer (To_Currency) it defaults to the CNY Lookup values, which come through correct.  But if "EUR" is selected in the slicer, it's still the "CNY" values populated.

Any idea on why this might be failing, or if there is some mistake in my formula above?

Best regards,

Alex

So my formula is:

Goal =

VAR __selectedSlicer = SELECTEDVALUE( 'Report Currency'[To_Currency] )

RETURN

IF( __selectedSlicer = "USD", 'Lessor/Lessee Schedule'[USD Lookup] , if( __selectedSlicer = "EUR", 'Lessor/Lessee Schedule'[EUR Lookup] , 'Lessor/Lessee Schedule'[CNY Lookup] ) )

[Anonymous]

Never mind my question...  but for other people's reference, the DAX needs to be a measure.  Perhaps that was obvious and I'm still too new to Power BI 🙂

[parry2k]

@Anonymous In my reply, I mentioned Amount Measure but glad issue is resolved and that is the best way to learn. Cheers!!

[Anonymous]

Thanks voor the quick reply, this worked exactly as I wanted. Much appreciated!