Returning a list of distinct rows that have been deselected by a slicer/filter

[AC4]

Hi All,

I have a tooltip on all pages in my report that shows what the user has selected/not selected on slicers on the landing page.

It works perfectly outside of the not selected "list", which currently concatenates all the "not selected" rows in the table, rather than just the distinct "not selected" rows in the table.

Part of the dax is below 

Selection RAG =

VAR SelectedValues =

    VALUES ( 'Account'[Account Group] )

VAR ConcatTable = CONCATENATEX (

            CALCULATETABLE ( SelectedValues ),

            'Account'[Account Group],

            ", "

        )

VAR ConcatTableExcept = CONCATENATEX (

            CALCULATETABLE ( Account, Except(All(Account[Account Group]), SelectedValues)),

            'Account'[Account Group],

            ", "

        )

The VAR ConcatTable works fine and concatenates values that have been selected in the slicer and is used when the number of selected values in the slicer is a low number

The VAR ConcatTableExcept is used when the number of deselected values in the slicer is low and number of selected values is high, except the problem is it returns all the rows in the table that havent been selected in the slicer rather than the distinct values that have not been selected.

I have tried to wrap the All inside of a distinct function like this 

Distinct(All(Account[Account Group])) and various other means but have not cracked the code yet.

Some example screenshots below.

When Sales Development is not selected (All rows containing Sales Development)

 

When only Sales Development is selected (correct)

Any help would be greatly appeciated

[Greg_Deckler · Accepted solution]

@AC4 Your ConcatTableExcept needs to be an EXCEPT of ALL('Table'[Column]) and your ConcatTable table.

[AC4]

Hi @Greg_Deckler Thanks!

I removed the table reference as the first argument from CALCULATETABLE and left the expression as the table to calculate.

VAR ConcatTableExcept = CONCATENATEX (

            CALCULATETABLE ( Distinct(Except((All(Reconciliator[Reconciliator Account Group])), (SelectedValues)))),

            'Reconciliator'[Reconciliator Account Group],

            ", "

        )