Ignore Filter in slicer

[Juramirez]

Hi all

I see many topics about this but i couldn't find the answer that i was looking. I have a slicer that has these values: USD and EUR. 

This are from a table where I have transactions (some are in EUR and others are in USD) but even if in my column Currency the transaction is in USD the column with the corresponding value in EUR exists (EURAmount), the same happens if the column Currency is in EUR -->(USDAmount). What I want to do is that when I select USD in my slicer, in a card visual i can see the sum of the column USDAmount and when I select EUR I can see the sum of EURAmount. The problem is that when i'm filtering this if I select USD it only shows me the values that are in that currency in the column Currency (It changes the sum but only for the values that have in my column Currency that value, not for all) Is possible to change the amount?

This is my current measure;

Total = IF(Table1[Currency]="USD",Table1[USDAmount],Table1[EURAmount])

Thanks

[Anonymous]

Create a table that has no relationships to anything else in your model that has two rows and one column; column values shoudld have USD and EUR. Use these values in your slicer and in your measure condition. Since they are not related to anything, it won't filter data out that you want to keep.

[Juramirez]

I did that and it keeps the transactions but it doesn't update de amount value regarding the currency. I mean, it keeps with the TRUE value of the IF function in the measure, I did this:

TotalWithNewTable=IF(SELECTEDVALUE(NewTable[Column1])="USD",Table1[USDAmount],Table1[EURAmount])

It is not working

[parry2k]

Are you getting error? Are you adding it as a measure or column? It need to be measure and you need to use aggregation method

[Juramirez]

@parry2k I'm adding it as a column, as a measure it doesn't see my column value. How can I do that?

[parry2k · Accepted solution]

Yes that is what i expected, as I mentioned earlier, you need to use aggregated method like sum

TotalWithNewTable=
IF(SELECTEDVALUE(NewTable[Column1])="USD",
SUM(Table1[USDAmount]),
SUM(Table1[EURAmount])
)

 and add it as a measure. 

[Juramirez]

Thanks @parry2k. It's working! 😄

[parry2k]

YAY, glad to hear 🙂

[skocheta]

Hey @parry2k, I am facing a similar issue and already using the solution similar to one suggested by you.

I have a separate currency table, with values as EUR, USD and SWE, this I use as a slicer.

I have a measure:

Total Sales (selected currency) =
SWITCH(TRUE(),
[Selected currency]="EUR", FORMAT([Total sales (sek)]*VALUES('Currency'[Currency Rate]),"€0,00"),
[Selected currency]="USD", FORMAT([Total sales (sek)]*VALUES('Currency'[Currency Rate]),"$0,00"),
FORMAT([Total sales (sek)],"0,00kr"))

This Works fine individually or in a card visual or pretty much anywhere. The issue is, When I add this to my original Scenario i.e. A Table view from Orders and Products table with Additional slicers on Product Id(from Orders) and Product Name(From Products), The Slicers do not function visually.

i.e. I get the correct value for Selected Product ID and name, but

1. I get something like a combination table with Product name repeated for all Product ID.

2. The Measure column gives blank values for all other rows (which shouldn't be there in first place)

I am very new to DAX, so kindly bare with me if this is a very basic lack of understanding.

PS: This is the sample pbix file, I am using the Sample PBIX fro Curbal here. https://1drv.ms/u/s!As-Rm1c3rV4gu1rozmeWqkqGPC4e

[parry2k]

Try to remove the format function from your Total Sales (selected currency)  measure and then test.

[skocheta]

Thanks @parry2k, That works. Could you explain How/Why 🙂

I was assuming that it was the else condition which was causing issue.

And on a different/related note.. Could you suggest a way so that this works with Formatting.

Alternately I created a Measure which displays selected Currency in a column, There the issue is when user does not select any currency (hence would ned a default option).

[parry2k]

@skochetaWhat would be the default option if no currency is selected?

[skocheta]

@parry2k, Euro,i was trying to use lastnonblank...

Also, Could you help how format was causing the issue and if there can be a work around.

My users would like to see which currency the figures are in (especially when they export to excel)