Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Dynamic single select slicer

  • 1.  Dynamic single select slicer

    Bronze Contributor
    Posted Nov 05, 2019 05:57 AM
    I need dynamic single select slicer for my report.

    Currently, I'm using normal slicer with single select. when i am filtering OUS - Market it has except $ currency. that case filter dynamically not changing to other currencies. (Note: i don't need multi select because that one gives wrong metrics to my card visual)
    Can you please suggest me the best slicer for my case.

    Thanks in advance!


    ------------------------------
    Ramar Veerasamy
    Associates Reports Developer

    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Dynamic single select slicer

    Bronze Contributor
    Posted Nov 05, 2019 06:59 AM
    Hi Ramar,
    If I understand correct, you need currency slicer updated based on market selected. If yes, then you need to map relationship between the fields. I can't comment in detail unless I see your data model

    ------------------------------
    Mohamed Ibrahim
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Dynamic single select slicer

    Bronze Contributor
    Posted Nov 06, 2019 06:19 AM
    Hello @Mohamed Ibrahim, thank u for your valuable time.
    I have added the Pbix file with sample data in Google drive: https://drive.google.com/open?id=1yoTzTFX0zWR4fmTAG3glOiuhkH2qYqgU
     currently i selected company - chennai & currency - INR. If I'm changing company to North America. Currency is not dynamically changing to ($ or €). it's dynamically needs to be changed.
    Can you please advice me



    ------------------------------
    Ramar Veerasamy
    Associates Reports Developer
    7708177403
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Dynamic single select slicer

    Bronze Contributor
    Posted Nov 06, 2019 06:52 AM
      |   view attached
    Thats because you have enabled single selection. Please turn that off to get the update. I am attaching the updated file

    ------------------------------
    Mohamed Ibrahim
    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 5.  RE: Dynamic single select slicer

    Silver Contributor
    Posted Nov 07, 2019 03:37 AM
    Hi Ramar,

    There are probably ways to do exactly what you asked and Mohamed's method may be one of those.

    I would however take your question from another angle: why do you need to filter by currency?

    If it is about showing the currency when having chosen a market, you could just have that taken care of in a formula!
    If it is about expressing the aounts in a given currency, then it is again formulas that should convert every amount.

    In both cases, you only need to show to the user, on top of the charts for instance, some message like: "All amounts expressed in ... ".

    Last case: if the requirement is to know the volume of business by currency (and area), then I would argue that you actually need the slicers to not auto-update each other because showing that there was no business in currency X for area Y is relevant information.

    Let me know what you think and good luck!

    ------------------------------
    Martin Dizel
    Project Management / BI Consultant
    Lysaker
    92014186
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Dynamic single select slicer

    Top Contributor
    Posted Nov 09, 2019 07:53 AM
    Hi @Ramar Veerasamy

    If you just want the slicer to show as you require within your current setup, have you tried using slicer level filter? For example, you can create a measure to count the rows of your relevant table and put that measure in the filter section of your slicer and enable only non-blank values. This should give you the desired result. ​

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Dynamic single select slicer

    Top Contributor
    Posted Nov 11, 2019 10:54 AM
      |   view attached
    Hi @Ramar Veerasamy

    I apologize. I do not think the setup I mentioned above will work in your case. Alternatively, what you can do is convert the currency slicer to a single column table visual. This will filter the currency according to the shop you select. Further, you can still slice the amounts by currency by selecting the relevant row in the table.

    PFA the pbix file.​

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 8.  RE: Dynamic single select slicer

    Bronze Contributor
    Posted Nov 12, 2019 02:39 AM
    Np. thanks for your precious time @Gopa Kumar Sivadasan..​

    ------------------------------
    Ramar Veerasamy
    Associates Reports Developer
    7708177403
    ------------------------------

    Conference-PBI_200x200


  • 9.  RE: Dynamic single select slicer

    Bronze Contributor
    Posted Nov 12, 2019 02:34 AM
    Hello @Martin Dizel​, Thanks a lot.

    Best Option: I don't have converted currency value in our DB. if i am adding measure for converting the all currency to $. then the report page & query loading taking time. Our user's won't accepts.

    Requirement: when applying the company filter then currency filter needs to automatically select the non blank currency

    ------------------------------
    Ramar Veerasamy
    Associates Reports Developer
    7708177403
    ------------------------------

    Conference-PBI_200x200