Forum Discussion
Find Distinct Data - Based On Two Columns
Hi rhcentennialh ,
We use the following measure and get the expected result:
Not identified by Outside = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Internal vs Outside] = "Internal" ), "c", [Code] ), BLANK (), "YES" )BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi rhcentennialh ,
Can it filter the data you want if we put the following measure into the Visual Filter then set the condition as greater than zero? Please try it without the measure in the value filed of table visual.
Not identified by Outside 2 = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Internal vs Outside] = "Internal" ), "c", [Code] ), 0 , 1 )Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi rhcentennialh ,
We can create a calculated table using following DAX:
Table 2 =
SELECTCOLUMNS (
FILTER (
ADDCOLUMNS (
DISTINCT (
SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Name] = "Name A" ), "Code-2", [Code] )
),
"CountOther", COUNTROWS (
FILTER ( 'Table', AND ( 'Table'[Name] <> "Name A", 'Table'[Code] = [Code-2] ) )
)
),
ISBLANK ( [CountOther] )
),
"Code", [Code-2]
)
Or we can add a calculated column to the table.
isDistinct =
VAR c = [Code]
VAR n = [Name]
RETURN
IF (
n = "Name A",
IF (
COUNTROWS (
FILTER ( ALL ( 'Table' ), AND ( 'Table'[Code] = c, 'Table'[Name] <> n ) )
) + 0 = 0,
"YES",
"No"
),
"NO"
)
If you want to get the dymanic result based on the slicer on user name, we can try the following measure:
isDistinct-Measure =
IF (
COUNTROWS (
FILTER (
ALL ( 'Table' ),
AND (
'Table'[Code] IN FILTERS ( 'Table'[Code] ),
NOT 'Table'[Name] IN FILTERS ( 'Table'[Name] )
)
)
) + 0 = 0,
"YES",
BLANK ()
)
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I don't believe this addresses my problem.
1.) I am needing to remove all the codes that "Name A" has identified.
2.) I am also needing to remove the Names associated with the codes "Name A" has identified. But only for those codes that have matched, any other Codes that were not also identified by "Name A" I want to keep.
For Example:
A.) "Name B" identified the following codes (2,8,9,10,18)
B.) "Name A" also identified code (18)
Result = I would want the table to show the Name & Codes (2,8,9,10). It will exclude (18) since it was captured by both "Name A" & "Name B"
Hi rhcentennialh ,
We can use the following measure to meet your requirement:
Not identified by A = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), [Name] = "Name A" ), "c", [Code] ), BLANK (), "YES" )Use this measure in table visual and add Slicer in Name Field.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you! This works for this small data sample, perfectly.
However, i am now running into another issue. When i try to apply this to my total data file (Hundreds of Names & Thousands of Codes) it is clearing my previous filters that narrow the data down to a specific name
Do you know what might be causing this?
This will provide more clarity, i hope. I have a filter in my reports that already filters down to the "Student" i want to look at. When i put the above formula in, it appears to break my Student Filter and give me all of the students in the table instead of only showing the unique codes for the identified student.
Sorry for the added confusion/complexity
Student Name Code Student 1 Name G 1 Student 1 Name C 1 Student 1 Name G 2 Student 1 Name F 2 Student 1 Name B 2 Student 1 Name G 3 Student 1 Name F 3 Student 1 Name A 3 Student 1 Name A 4 Student 1 Name A 5 Student 1 Name A 6 Student 1 Name A 7 Student 1 Name B 8 Student 2 Name B 9 Student 2 Name F 10 Student 2 Name B 10 Student 2 Name F 11 Student 2 Name G 12 Student 2 Name A 13 Student 2 Name D 14 Student 2 Name G 15 Student 2 Name D 15 Student 2 Name C 16 Student 2 Name A 17 Student 2 Name G 18 Student 2 Name A 18 Student 2 Name B 18 Student 2 Name H 19 Student 2 Name G 19