skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led Training
      • Overview
      • Guided learning
      • Documentation
      • Webinars
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Galleries
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • MBAS Gallery
    • MBAS Gallery 2020
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • The Complex Selector

    The Complex Selector

    05-24-2020 10:52 AM

    Super User IV Greg_Deckler
    Super User IV
    1212 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User IV Greg_Deckler
    Super User IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    The Complex Selector

    ‎05-24-2020 10:52 AM

    The Complex Selector is more a technique than a single measure. You use a Complex Selector when you want to filter visuals based upon end user input, such as a selection in a slicer. As everyone will have different selection criteria, this Quick Measure submission demonstrates the general approach to achieve this. Since just about any complex logic can be implemented using this technique, it is simply not possible to cover every possible permutation.

     

    It should be noted that The Complex Selector is really a general form of the Inverse Selector, or perhaps it is better said that the Inverse Selector is a specific application of The Complex Selector's technique. Regardless, here it is.

     

    There are three examples on three different pages included in the report. The first page shows a Clustered Column chart and a basic implementation of The Complex Selector. Here the goal is to display the last four (4) weeks of data based upon the user's selection in the slicer.

    Complex Selector = 
        VAR __SlicerWeek = MAX('Weeknums'[Weeknum])
        VAR __Min = __SlicerWeek - 4
        VAR __CurrentWeek = MAX('Table'[Weeknum])
    RETURN
        IF(__CurrentWeek > __Min && __CurrentWeek <= __SlicerWeek,1,0)

    The second page demonstrates how to use our Complex Selector in a matrix visualization. Note that in the formula Complex Selector 2 is the same formula as Complex Selector.

    Matrix Value = 
        VAR __ValueToShow = SUM('Table 2'[Column])
    RETURN
        IF([Complex Selector 2]=1,__ValueToShow,BLANK())

    Finally, the third page demonstrates using a complex select in coordination with another complex selector. Here we see how we can create a very complex criteria for showing values. In the example provided, we want the last four weeks but only the top 10, but, we want the top 10 for each week. Texas is only in the top 10 for week 13. Note that in the formula Complex Selector 2 is the same formula as Complex Selector.

    Doubly Complex = 
        VAR __Week = MAX('Table 2'[Weeknum])
        VAR __State = MAX('Table 2'[State])
        VAR __Value = SUM('Table 2'[Column])
        VAR __Table = 
            GROUPBY(
                FILTER(ALL('Table 2'),[Weeknum] = __Week),
                [State],
                "Value",SUMX(CURRENTGROUP(),[Column])
            )
        VAR __Count = COUNTROWS(FILTER(__Table,[Value] >= __Value))
        VAR __ValueToShow = IF(__Count <= 10,__Value,BLANK())
    RETURN
        IF([Complex Selector 2]=1,__ValueToShow,BLANK())

     

    eyJrIjoiMjgxOTUzNzYtMTgxYy00Njc1LThiZGMtMTNiNjk5N2QzNzNiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    ---------------------------------------

    @ me in replies or I'll lose your thread!!!

    I have a NEW book! 
    DAX Cookbook from Packt
    Over 120 DAX Recipes!




    Did I answer your question? Mark my post as a solution!

    Proud to be a Super User!




    Preview file
    130 KB
    ComplexSelectorFinal.pbix
    Labels:
    • Labels:
    • Filters
    • Other
    Message 1 of 1
    1,212 Views
    4
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Preview Exit Preview

    never-displayed

    You must be signed in to add attachments

    never-displayed

    Additional options
    You do not have permission to remove this product association.
     

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    • © 2021 Microsoft
    • Follow Power BI
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks