Returning a list of distinct rows that have been deselected by a slicer/filter
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
Finding duplicate rows using DAX
My scenario is a much larger dataset with longer strings for the two columns, but this should be generalizable so for simplicity, let's say I have a base table with the following data: Group Name Member Name Group A Alex Group A Betty Group A Charles Group B Alex Group B Betty Group C Alex Group C Betty Group C Charles I know how to make a measure that concatenates the members into a single string: Member List = CONCATENATEX( VALUES(Example[Member Name]), Example[Member Name], ", ", Example[Member Name], ASC ) Group Name Member List Group A Alex, Betty, Charles Group B Alex, Betty Group C Alex, Betty, Charles What I would like to be able to do is have a way to do a distinct on that Member List column so that I can find duplicates. I'd also like to do it using a temp table and not one in storage since I actually have something that's thousands of rows and the Member List string is going to be thousands of characters, hence the DAX preference. So I'm looking for something like: Group Name isDuplicate Group A 1 Group B 0 Group C 1 Any suggestions? I've tried to use ADDCOLUMNS(SUMMARIZE( ... where I add the concatenatex as an additional column, but when I try to do something like DISTINCTCOUNT on that new column, I get errors.
