Filter a visual by a measure against a disconnected table

[PBIM]

I have calculated a measure (Attendance Band), somewhat nested, to calculate a band (by looking up against a disconnected band threshold table).

How can i filter a table of all students by clicking on my band table visual.

Because it's disconnected, it seems incredibly difficult but maybe I'm missing something simple.  I tried to use TREATAS and dabbled with creating a calculated table but that doesn't seem to adjust to context.

How can I achieve what I need to (or otherwise present some end-user-intuitive method of interogating students in a band.

Thanks in adavnce.

https://drive.google.com/file/d/1dno3l4WvTm_tzMKlv1zpwoSQI9YPJ-gB/view?usp=sharing 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[shafiz_p · Accepted solution]

Hi @PBIM  Firstly, on interaction between the 2 table which is currently set off. See image:

Try update your Attendance Band measure with the following code:

Attendance Band = 
VAR PercentValue = [Present_AEA %]

VAR SelectedBand = SELECTEDVALUE('Attendance Bands'[Description])

VAR _LessThan100bands =         
CALCULATE(
    MAX('Attendance Bands'[Description]),
    FILTER(
        'Attendance Bands',
        PercentValue >= 'Attendance Bands'[MinValue] / 100 && 
        PercentValue < 'Attendance Bands'[MaxValue] / 100 &&
        'Attendance Bands'[Description] = SelectedBand
    )
)

VAR _Only100Bands =
CALCULATE(
    MAX('Attendance Bands'[Description]),
    FILTER(
        'Attendance Bands',
        PercentValue >= 'Attendance Bands'[MinValue] / 100 && 
        PercentValue <= 'Attendance Bands'[MaxValue] / 100 &&
        'Attendance Bands'[Description] = SelectedBand
    )
)

VAR _general =
IF(
    PercentValue = 1,
    "95 to 100",
    CALCULATE(
        MAX('Attendance Bands'[Description]),
        FILTER(
            'Attendance Bands',
            PercentValue >= 'Attendance Bands'[MinValue] / 100 && 
            PercentValue < 'Attendance Bands'[MaxValue] / 100
        )
    )
)

RETURN
IF(
    ISFILTERED('Attendance Bands'[Description]),
    IF(
        SelectedBand = "95 to 100",
        _Only100Bands,
        _LessThan100bands
    ),
    _general
)

Now go to filter option and set attendence Band is not blank. See image:

You are done. See output:

1. No Selection is made in the band table:

2. When selecting in the band table:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[PBIM]

That is amazing.  Thank you.  Please would you mind explaining a bit about the new measure you created or morever what the fundamental difference in approach is.
Many thanks again

[shafiz_p]

Basically not much difference except hard code for 100%. You need to remove hard code, otherwise, this will always visible regardless of your selection. 

For example, if you select 0 to 50, then you will see , 0 to 50 and all the 95 to 100 for 100%.

If you include both lower and upper limit and remove hard code, then it would overlap with the current setup of Bands. For example, 50 will go to both 0 to 50 and 50 to 100.

To overcome this challenge, you need to divide the bands into 2 segments. One segment is less than 95 which will include lower but exclude upper limit and other segment will be greater than 95 which will include both lower & upper limit. 

Now this 2 parts will show results based on selection. If selection made (Isfiltered), then 2 possible choice, if not, then only a single choice (Show All).

Without this : Attendance Bands[Description] = SelectedBands , formula would also work.

Hope this clearify your understanding.

[PBIM]

Oh.  I see.  Thanks.  Did I give myself a headacje by the 100% issue then?

I could set all max to 94.99, 89.99 etc

In your opinion, is that worthwhile to make everything a lot simpler?