Filtering other tables based on cross-highlighting a filtered measure

Question

I want to count records in a certain condition and allow people to filter down to the relevant records, but selecting a measure value (which filters so it only counts certain rows) isn't cross-filtering others as I'd expect. Maybe ths isn't possible or maybe I'm just doing it wrong, but I'd appreciate help.

I have a single table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTJUitVBEjICChmgChljCplgajSFCMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Ordered Recently" = _t]),
    #"Change to INT" = Table.TransformColumnTypes(Source,{{"Ordered Recently", Int64.Type}}),
    #"Change to T/F" = Table.TransformColumnTypes(#"Change to INT",{{"Ordered Recently", type logical}})
in
    #"Change to T/F"

The result looks like this:

Customer Name   Ordered Recently
Customer 1      True
Customer 2      False
Customer 3      False
Customer 4      True
Customer 5      True

I added two measures:

Count Total = COUNTROWS(Customers)

Count Recent = CALCULATE([Count Total], filter(Customers, Customers[Ordered Recently]))

If I put both measures in a bar chart and highlight the "Count Recent" measure, I'd expect it to know to filter other visuals based on the FILTER statement present in the measure, but that doesn't happen. Selecing this value doesn't impact anything else on my page (including just a count of rows).

The goal is to allow people to select a measure that counts rows and then to see the makeup of the data in those rows (select a count of late projects and filter other visuals to describe those late projects).

Is this possible or am I doing something wrong?

EXAMPLE:

Here's what it looks like now, with nothing selected:

When I select the black bar (the "Ordered Recently" measure), nothing changes right now - but here's what I want to happen (actually achieved with a slicer off screen on the T/F field):

I understand if my measure is a SUM of an integer field, it includes every row in the calculation - even when the row value is zero - and there's no way to filter my dataset based on that. However, in this case, my measure is actually using a FILTER on the dataset so that it only counts rows with a certain criteria set - given that, it should be able to filter the requested table, and then flow that filter through the rest of my dataset (the same way it would if I selected a bar from a chart where I had used that same field as the series - exactly how it works when I do this:

PBIX file to download as an example

Can you edit in some kind of mock-up for how it would ideally work for what you want (by manually setting filters perhaps)? I want to make sure I'm fully understanding the use case before making further recommendations. — Alexis Olson
@AlexisOlson I've added some mock-up screenshots to show what happens and what I want to make happen. I realize that in the case of a simple SUM, it acts on every row in the dataset and there's no way to filter based on that. However, when I'm using a FILTER statement in my measure and it's only executing the measure on a subset of the rows, I want Power BI to flow that filter back through the dataset as it does when my field is on the axis and I select it (example also shown there). Thanks! — SqlRyan

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-26T16:50:30

No, I don't believe it's possible to make a measure value cross-filter other visuals based on filters within the measure definition.

You can, however, click on i.e. row header Customer 3 and it should cross-filter the other visuals to only include that customer. Any table column you set for the rows or columns of a matrix visual should behave this way.

Here's a hacky workaround:

Create a measure that shows the right values when you use the column you want to use as a filter as the Legend or Axis (like in your last image). For example, in this case, you could do this:

Total Customers =
VAR TF = SELECTEDVALUE ( Customers[Ordered Recently] )
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Customers ),
            IF ( TF, TF, TF || Customers[Ordered Recently] )
        )
    )

This behaves how you want, but isn't labeled as you want. To achieve that create a calculated column with the labels you want. For example,

Label = IF(Customers[Ordered Recently], "Ordered Recently", "Total Customers")

Then take Ordered Recently off the axis and put the Label column in the Legend box to get this:

Filtering other tables based on cross-highlighting a filtered measure

2 Answers