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

AC4 — Thu, 18 Apr 2024 15:45:58 GMT

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

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

Greg_Deckler — Tue, 16 Jan 2024 18:56:42 GMT

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

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

AC4 — Wed, 17 Jan 2024 16:11:05 GMT

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],

", "

)

topic Re: Returning a list of distinct rows that have been deselected by a slicer/filter in DAX Commands and Tips

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

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

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