Filtering across tables with Visuals for Progressive Data.

[v-merpet]

I am trying to see all of my Members that have come thru my process. When I select Team B here I want to see all of the tenants that are in Team A as well as Team C if they transitioned. If they came in on Team B I want just that data but if in any instance of Team A or C they appear I want them to render.Pre selection

Here we want to see the 12 that are in Team B and any of those 12 that came from Team A as well As any that went to Team C.

Post Selection

However because of the Table level filters to demonstrate the Teams I am not able to determine how to create a view that presents a progressive session. 

[v-shex-msft · Accepted solution]

HI @v-merpet,

Current, slicer/filters do not support except filter effect.
I'd like to suggest you write a measure with except function to get the reverse result as a filter and use on the visual level filter of your visuals. (please break the relationship from the slicer to current visual)

Measure =
VAR selected =
    VALUES ( Selector[Column] )
VAR revSelected =
    EXCEPT ( ALL ( Table[Column] ), selected )
VAR currItem =
    SELECTEDVALUE ( table[column] )
RETURN
    IF ( currItem IN revSelected, "Y", "N" )

Regards,

Xiaoxin Sheng

[v-merpet]

Pure Genius, This worked perfectly and was so elegant. Thank you so much for teaching me more about this function.

[amitchandak]

Can you share data model and sample data

[v-merpet]

This is testing data and it lives on a single table for a feasability study. One Table - Two columns : Team Name and Client Name. The Progression Breakout Table is Team to Count of Tenant Name. Tables A B and C are Tenant Name filtered by associated Team. So a client can start in Team B and progress to Team C. Or they can be in Team A and move through B to C. (No current additional tables and no relationships engaged. Always forward progression as later dates we will be adding more team sequences.) 

** I corrected the name on this visual to eliminate any confusion.

[v-merpet]

Hey I think you might have answered this before and I just stumbled across it here:

https://docs.microsoft.com/en-us/dax/except-function-dax

Thoughts?

- M