cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Ignore Filter in slicer

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

1 ACCEPTED SOLUTION
Super User

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

12 REPLIES 12
Anonymous
Not applicable

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.

Resolver I

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Resolver I

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

Super User

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Resolver I

Thanks @parry2k. It's working! 😄

Super User

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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).

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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)