The Complex Selector 05-24-2020 10:52 AM

[Greg_Deckler]

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