Dynamic Slicer Value Population

Question

Hello All,&nbsp;I have the below requirement.&nbsp;We have currencies at three levels.Global - USD YENRegional - USD YEN EUROLocal - USD YEN POUNDS FRANC&nbsp;&nbsp;&nbsp;&nbsp;The Currency Single Select Filter changes the currency format to the selected one.USD is default.&nbsp;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 JPY2)When One region is selected,example EMEA ,then currency slicer should show the regional curreny along with global currency - USD,JPY and EUR3)When one region and one country is selected then the local currency should also be considered - USD.JPY,EUR and POUNDS4)When two regions are selected in a region then the local currency should not be available- USD,JPY and EUR5)When Two regions are selcted then show only global currency - USD,JPY&nbsp;Filter 1&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Filter 2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Target Slicer (Single select)1)Region ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Country ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Currency- USD JPY2)Region-EMEA(Single)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Country ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Currency - USD JPY EUR3)Region-EMEA&nbsp;(Single)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Country England(Single)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Currency - USD JPY EUR POUNDS4)Region-ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Country England and France(multiple)&nbsp; &nbsp; Currency -USD JPY EUR&nbsp;5)Region-AMERICAS and EMEA(multiple)&nbsp; &nbsp; &nbsp; &nbsp; Country -All or multiple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Currency - USD JPY&nbsp;The Target slicer should change/populate accordingly&nbsp;PLease let me know if anyone has a solution for this

nizaminzi · Answer

Please find attached the sample file.&nbsp;&nbsp;There are two tables.One for Region,Country and one for Currency.Currency is a disconnected table.&nbsp;Country Key Country RegionJP Japan JAPANUS USA AMERICASCA Canada AMERICASMX Mexico AMERICASFR France EMEAIL Israel EMEAIT Italy EMEANL Netherlands EMEAPL Poland EMEADE Germany EMEAAU Australia APACIN India APACMY Malaysia APACNP Nepal APACSG Singapore APACNZ New Zealand APACCN China CHINA&nbsp;CurrencyUSDJPYEURPOUNDS&nbsp;we need to change currency table according to selections made on region and country from the region table.Reagrds,Nizam

v-janeyg-msft · Answer

Hi,&nbsp;nizaminzi&nbsp;&nbsp;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] ) ) &amp;&amp; NOT ( ISFILTERED ( 'Table'[Region] ) ), "Currency- USD JPY",
    SELECTEDVALUE ( 'Table'[Region] ) = "EMEA"
        &amp;&amp; NOT ( ISFILTERED ( 'Table 1'[Country] ) ), "Currency - USD JPY EUR",
    SELECTEDVALUE ( 'Table'[Region] ) = "EMEA"
        &amp;&amp; SELECTEDVALUE ( 'Table 1'[Country] ) = "England", "Currency - USD JPY EUR POUNDS",
    NOT ( ISFILTERED ( 'Table'[Region] ) )
        &amp;&amp; "England"
            IN DISTINCT ( 'Table 1'[Country] )
                &amp;&amp; "France"
                    IN DISTINCT ( 'Table 1'[Country] )
                        &amp;&amp; COUNTROWS ( DISTINCT ( 'Table 1'[Country] ) ) = 2, " Currency -USD JPY EUR",
    "AMERICAS"
        IN DISTINCT ( 'Table'[Region] )
            &amp;&amp; "EMEA"
                IN DISTINCT ( 'Table'[Region] )
                    &amp;&amp; COUNTROWS ( DISTINCT ( 'Table'[Region] ) ) = 2
                    &amp;&amp; COUNTROWS ( DISTINCT ( 'Table 1'[Country] ) ) &gt; 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.&nbsp;Best RegardsJaney Guo&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

nizaminzi · Answer

v-janeyg-msft&nbsp;&nbsp;Thanks for the reply!&nbsp;But I was looking for a different solutionI should have represented data in a better way.&nbsp;My requirement is when i select region EMEA and country all, then currency slicer should be like below.&nbsp;Currency SlicerUSDJPYEURThis should give us an option to select one currency.&nbsp;If I have selected multiple regions, then my currency filter should be like below.&nbsp;Currency FilterUSD&nbsp;JPYI hope I am clear here!

v-janeyg-msft · Answer

Hi,&nbsp;nizaminzi&nbsp;&nbsp;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&nbsp;you share some sample data for reference?So we can help you soon.&nbsp;Best RegardsJaney Guo&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

v-janeyg-msft · Answer

Hi,&nbsp;nizaminzi&nbsp;&nbsp;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?&nbsp;So I can give you a suitable workaround.Thank you.&nbsp;Best RegardsJaney Guo&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Dynamic Slicer Value Population

9 Replies

Related content

Population dynamic graaph

Dynamic Subtitle with Hierarchy Slicers

Power BI Dynamic Filter/Slicer

Show slicers dynamically

Dynamic Complex Filtering with slicers