How to use a filtered selection in a matrix to filter another visual

[ct_tm]

In my very large corporate dataset, we use an internal scoring metric to rate properties from A-E. Until now, I pre-calculated the scoring in a calculated table that was loaded on report refresh and that worked out well until it started consuming more memory than is available in our environment and began causing report refresh failure. The reason I used this method was to build a scoring matrix, since I needed the scores as column headers and it worked out well. This method allowed me (with a little finesse) to click the intersection of dimension and rating to filter another visual in the way that I wanted. If I clicked a B value of 3 for a row, in the associated widget I see the three items because I can match it up easily with the column count from the pre-computed table.

Here is how the matrix looks currently:

So as I rework things, since i can no longer pre-compute the table due to it exceeding max resources during report refresh in the online service I decided to make counting measures for each column. Here is an example of one of the measures:

Count_A = CALCULATE(
    COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'General Community Information',
                    'General Community Information'[PropertyHMY]
                ),
                "Total FOCUS Rating", [Total FOCUS Rating (Rollup)]
            ),
            [Total FOCUS Rating] = "A"
        )
    )
)

This works to count the dimension row, but does not filter the related widget correctly. I have tried working with Co-Pilot but as anyone who has ever used it knows, once you get stuck down a rabbit hole it continues to spit out the same bad code over and over regardless of direction. How can I use this new setup, where each column is a measure that counts how many ratings = whatever letter I am checking against to filter another widget based on the intersecting selection of row and column. As it works now in my test file, if I click a square I get all the values associated with the row not just the items that match the count i clicked on.

So, in the screenshot below I would get all the properties associated with the row selected, so instead of seeing the 3 in  the count of A I highlighted, I would get the results of all 22 (3+4+3+1+11, A-E respectively)

Co-Pilot keeps spitting out code that might work with a physical table, so I am looking to you all for some help since that doesn't work anymore.

Thanks!

[johnt75]

If I understand correctly you want to show monthly values for items when any month in the selected period has the appropriate ranking. I think you can modify the filter measure to 

Item is visible =
IF (
    ISFILTERED ( 'FOCUS Scoring'[Total Rating] ),
    VAR DatesToUse =
        CALCULATETABLE (
            VALUES ( 'Calendar'[MonthYearNum] ),
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[FutureDate] = "Past"
        )
    VAR Ratings =
        SELECTCOLUMNS ( DatesToUse, "@rating", [Total FOCUS Rating (Rollup)] )
    VAR Result =
        IF ( SELECTEDVALUE ( 'FOCUS Scoring'[Total Rating] ) IN Ratings, 1 )
    RETURN
        Result,
    1
)

[ct_tm]

@johnt75 Thanks again for replying and your help. That doesn't quite do it, but I did learn something new from the structure of this measure!

This is a property management compnay, so all dimensions essentially come back to the property itself. That PK is propertyHMY. So, say we click on a matrix item where the count represents the count as of the latest value in the date slicer. The propertyHMYs associated with that count in the matrix should be shown in the corresponding table with all the months in the slicer shown, regardless of the ratings of the other months for those properties counted in the matrix selection. So say one of the properties in the count has an A for December but every other month was a B. I want to see all those B months when I expand the row.

I've tried working with it a little bit this morning, but since you did a thing I've not seen before I am not really sure how to make the necessary adjustment.

[johnt75]

Item is visible =
IF (
    ISFILTERED ( 'FOCUS Scoring'[Total Rating] ),
    VAR DatesToUse =
        CALCULATETABLE (
            VALUES ( 'Calendar'[MonthYearNum] ),
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[FutureDate] = "Past"
        )
    VAR Ratings =
        SELECTCOLUMNS (
            DatesToUse,
            "@rating",
                CALCULATE (
                    [Total FOCUS Rating (Rollup)],
                    ALLEXCEPT ( 'Calendar', 'Calendar'[MonthYearNum] )
                )
        )
    VAR Result =
        IF ( SELECTEDVALUE ( 'FOCUS Scoring'[Total Rating] ) IN Ratings, 1 )
    RETURN
        Result,
    1
)

I tweaked it to remove any filters on the date table other than the year month number.

I'll give an explanation of what it is doing to help understanding.

First it builds a table of all visible months, based on any slicer selection. For each visible month it calculates the rating for the property which is currently in the filter context.

If any of the calculated ratings match the selected rating, which is in the filter context because you selected it in the matrix, then it will return 1.

This should work, I think, if you use it as a filter on the second matrix that you are looking to cross highlight.

[ct_tm]

@johnt75 While that is allowing for the expansion of the row to show all months, its only showing a rating for the same letter selected in the matrix in those months. In addition, it will also filter to show properties that had that same letter in the past, but not as of the latest month. It should show only properties where the latest month matches the selected rating and row in the matrix, then be drillable to show all months for that property regardless of rating in the cross highlighted table. 

[johnt75]

Try

Items is visible =
IF (
    ISFILTERED ( 'FOCUS Scoring'[Total Rating] ),
    VAR LatestDate =
        MAX ( 'Calendar'[Year month number] )
    VAR Rating =
        CALCULATE (
            [Total FOCUS Rating (Rollup)],
            REMOVEFILTERS ( 'Calendar' ),
            'Calendar'[MonthYearNum] = LatestDate
        )
    VAR Result =
        IF ( Rating = SELECTEDVALUE ( 'FOCUS Scoring'[Total Rating] ), 1 )
    RETURN
        Result,
    1
)

[ct_tm]

@johnt75 That didn't work and in fact gave some curious results. The original measure you gave me works a charm for cross filtering, minus the ability to expand to the months. If I select say a D with a count of 6 in the matrix I see those 6 properties. I think with enough time I can take the additional tries you provided and come up with a working solution. I appreciate your help!

[johnt75 · Accepted solution]

One approach would be to create a disconnected table which just contains the ratings A-E. Use this as the columns in your matrix visual and create a measure like

Count Ratings =
VAR Rating =
    SELECTEDVALUE ( 'Ratings'[Value] )
VAR Result =
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        'General Community Information',
                        'General Community Information'[PropertyHMY]
                    ),
                    "Total FOCUS Rating", [Total FOCUS Rating (Rollup)]
                ),
                [Total FOCUS Rating] = Rating
            )
        )
    )
RETURN
    Result

Then create another measure like

Item is visible =
IF ( [Total FOCUS Rating (Rollup)] = SELECTEDVALUE ( Rating[Value] ), 1 )

and use this as a filter on the visual you want to crossfilter, set to only show when the value is 1.

[ct_tm]

@johnt75 Thank you, I think that's what co-pilot was trying to get to but just kept getting hung up on wanting a physical table. 

I did modify the last measure slightly so that if nothing was filtered it would still display the whole table:

I don't know if you want to edit your post and update it so i can accept it as the solution or if that even matters.

Item is visible = 
IF(
    ISFILTERED('FOCUS Scoring'[Total Rating]),
    IF ( [Total FOCUS Rating (Rollup)] = SELECTEDVALUE ( 'FOCUS Scoring'[Total Rating] ), 1 ),
    1
)

One additional thing, if you have the time. The way I originally structured it is so that the item that this matrix filtered would show all of the results that were included in the date slicer instead of just filtering to show where the letter grade = the selected letter. So if it were December and you selected an "A" and have all of 2024 selected in the date slicer it would show all the values Jan-Dec instead of filtering to just the months where the score was A.

So here is one of the pieces that makes up the total scoring done from the old pre-computed table that let me do just that. Since I no longer have the physical computed table what modification would I need to make to re-enable this concept?

Liquidity Rating (Grouping) = 
VAR maxDate = CALCULATE(MAX('Calendar'[MonthYearNum]), FILTER(ALLSELECTED('Calendar'), 'Calendar'[FutureDate] = "Past"))
VAR filt =
    CALCULATETABLE(
        VALUES(Rollup_FOCUS_Totals[PropertyHMY]),
        ALLNOBLANKROW('Calendar'),
        //This filter pegs the value to the max date. 
        FILTER(ALLEXCEPT(Rollup_FOCUS_Totals, Rollup_FOCUS_Totals[Total Rating]), Rollup_FOCUS_Totals[MonthYearNum] = maxDate)
    )
RETURN    
CALCULATE(
    [Liquidity Rating (As Of)],
    'General Community Information'[PropertyHMY] in filt,
    FILTER('Calendar', 'Calendar'[MonthYearNum] = MAX('Calendar'[MonthYearNum]))
)