March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello All,
I have the below requirement.
We have currencies at three levels.
Global - USD YEN
Regional - USD YEN EURO
Local - USD YEN POUNDS FRANC
The Currency Single Select Filter changes the currency format to the selected one.USD is default.
My requirement is as below:
1)When Region is all and country is all then currency slicer should show only the global currencies - USD and JPY
2)When One region is selected,example EMEA ,then currency slicer should show the regional curreny along with global currency - USD,JPY and EUR
3)When one region and one country is selected then the local currency should also be considered - USD.JPY,EUR and POUNDS
4)When two regions are selected in a region then the local currency should not be available- USD,JPY and EUR
5)When Two regions are selcted then show only global currency - USD,JPY
Filter 1 Filter 2 Target Slicer (Single select)
1)Region ALL Country ALL Currency- USD JPY
2)Region-EMEA(Single) Country ALL Currency - USD JPY EUR
3)Region-EMEA (Single) Country England(Single) Currency - USD JPY EUR POUNDS
4)Region-ALL Country England and France(multiple) Currency -USD JPY EUR
5)Region-AMERICAS and EMEA(multiple) Country -All or multiple Currency - USD JPY
The Target slicer should change/populate accordingly
PLease let me know if anyone has a solution for this
Yes you can say that too,If a person belongs to england she should not view view YUAN which belongs to china,HE SHOULD VIEW ONLY USD,JPY,EUR,POUNDS.If two countries are selected(like england and france) then there should be only regional currency(like EURO ALONG WITH USD,JPY) and no country currencies.
Hi, @nizaminzi
If the user can choose the region arbitrarily and can switch after the choice, Don’t you think your requirements are actually useless?
Hi, @nizaminzi
It’s my pleasure to answer for you.
According to your description,I think dynamic slicer value is not feasible, but you can use a measure in the card to achieve a similar result.
Like this:
Measure =
SWITCH (
TRUE (),
NOT ( ISFILTERED ( 'Table 1'[Country] ) ) && NOT ( ISFILTERED ( 'Table'[Region] ) ), "Currency- USD JPY",
SELECTEDVALUE ( 'Table'[Region] ) = "EMEA"
&& NOT ( ISFILTERED ( 'Table 1'[Country] ) ), "Currency - USD JPY EUR",
SELECTEDVALUE ( 'Table'[Region] ) = "EMEA"
&& SELECTEDVALUE ( 'Table 1'[Country] ) = "England", "Currency - USD JPY EUR POUNDS",
NOT ( ISFILTERED ( 'Table'[Region] ) )
&& "England"
IN DISTINCT ( 'Table 1'[Country] )
&& "France"
IN DISTINCT ( 'Table 1'[Country] )
&& COUNTROWS ( DISTINCT ( 'Table 1'[Country] ) ) = 2, " Currency -USD JPY EUR",
"AMERICAS"
IN DISTINCT ( 'Table'[Region] )
&& "EMEA"
IN DISTINCT ( 'Table'[Region] )
&& COUNTROWS ( DISTINCT ( 'Table'[Region] ) ) = 2
&& COUNTROWS ( DISTINCT ( 'Table 1'[Country] ) ) > 1, "Currency - USD JPY"
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply!
But I was looking for a different solutionI should have represented data in a better way.
My requirement is when i select region EMEA and country all, then currency slicer should be like below.
Currency Slicer
This should give us an option to select one currency.
If I have selected multiple regions, then my currency filter should be like below.
Currency Filter
I hope I am clear here!
Hi, @nizaminzi
I have not seen what you want to use the Currency slicer for from your description, if it is only used for presentation, it can be replaced by card, which can be dynamic.
Could you share some sample data for reference?So we can help you soon.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please find attached the sample file.
There are two tables.One for Region,Country and one for Currency.Currency is a disconnected table.
Country Key Country Region
JP Japan JAPAN
US USA AMERICAS
CA Canada AMERICAS
MX Mexico AMERICAS
FR France EMEA
IL Israel EMEA
IT Italy EMEA
NL Netherlands EMEA
PL Poland EMEA
DE Germany EMEA
AU Australia APAC
IN India APAC
MY Malaysia APAC
NP Nepal APAC
SG Singapore APAC
NZ New Zealand APAC
CN China CHINA
Currency
USD
JPY
EUR
POUNDS
we need to change currency table according to selections made on region and country from the region table.
Reagrds,
Nizam
Hi, @nizaminzi
I was very busy yesterday,but now I have checked your needs and I find some problems.
First, I have reminded you in the last reply that it is impossible to dynamically change the value in sclier, because this is equivalent to dynamically changing the value of a column in your data source. So can you understand it?
If you want to change dynamically, you can only use measure. Can you fully describe your reqiuirements and what you want the currency slicer to do? So I can give you a suitable workaround.Thank you.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft ,
I understand.So from your explanation I see that calculated columns approach is not possible.
My exact requirement is as follows.
Requirement
I hope this is clear.If a workaround is possible then please suggest.
Hi, @nizaminzi
Do you want to prohibit users in the local area from choosing non-corresponding currency?
Best Regards
Janey Guo
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |