Forum Discussion
Slicer to switch between 2 columns
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.
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] ) )
5 Replies
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] ) )
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] )RETURNIF( __selectedSlicer = "USD", 'Lessor/Lessee Schedule'[USD Lookup] , if( __selectedSlicer = "EUR", 'Lessor/Lessee Schedule'[EUR Lookup] , 'Lessor/Lessee Schedule'[CNY Lookup] ) )