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 ,
I am sorry for missing this situation, we can use the following measure to meet your requirement, If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Not identified by A =
IF (
SELECTEDVALUE ( 'Table'[Code] )
IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Name] = "Name A" ), "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.
I believe we are very close to addressing my problem. I am still having the issue of the current filters being broken when applying the measure. Please see below for another example of the data as well as the expected result. As a reminder i am wanting to remove the codes identified by "Internal" as well as the "Outside" line items that had codes identified by both "Internal" & "Outside". The end result giving me only the unique codes identified by "Outside".
Note the following:
1.) Internal vs Outside is a calculated column to help identify where the code came from
2.) Student column is Pre-Filterd from another page that is highlighting a specific student, there are thousands of students we can select from in the full database
Sample Data Set
| Internal vs Outside | Student | Code |
| Internal | Student 1 | 1 |
| Outside | Student 1 | 1 |
| Outside | Student 1 | 2 |
| Internal | Student 1 | 3 |
| Outside | Student 1 | 4 |
| Outside | Student 1 | 5 |
| Outside | Student 1 | 6 |
| Outside | Student 1 | 7 |
| Internal | Student 1 | 8 |
| Outside | Student 1 | 8 |
| Outside | Student 1 | 9 |
Expected Result
| Internal vs Outside | Student | Code |
| Outside | Student 1 | 2 |
| Outside | Student 1 | 4 |
| Outside | Student 1 | 5 |
| Outside | Student 1 | 6 |
| Outside | Student 1 | 7 |
| Outside | Student 1 | 9 |
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 ,
How about the result after you follow the suggestions mentioned in my previous post?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.Would you be able to provide the .pbix file again. I would like to reference this file again.
Hi rhcentennialh ,
Sure, 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 found what my issue is, my "Internal vs Outside" column in the actual file is a calculated column that does a simple IF statement to determine what bucket the code should fall in. When i try to input the solution into my file this calculated column appears to break it.
Do you have a solution for doing the same thing but using a calculated column for "Internal vs Outside"?
Hi rhcentennialh ,
The formula should also work if the "Internal vs Outside" is a calculated column, maybe you mean it is a measure? We can try to use the following formula :
Not identified by Outside = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "IO", [Internal vs Outside] ), [IO] = "Internal" ), "c", [Code] ), BLANK (), "YES" )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 am still running into issues, i know its something i am missing on my end.
However, could i change the formula you previously provided (Below) to reference a Non-Calculated column? Instead of [Internal vs Outside] i would change this to [Teacher]. I have a list of 5 teachers that i need to reference that would reflect the Internal vs Outside group. Could i do the following with an OR statement? Please let me know what you think.
New Solution with a OR Statement
Not identified by Outside = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Teacher] = or("Teacher 1", or("Teacher 2", or("Teacher 3",
or("Teacher 4", "Teacher 5"))))
), "c", [Code] ), BLANK (), "YES" )Previous Provided Solution
Not identified by Outside = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Internal vs Outside] = "Internal" ), "c", [Code] ), BLANK (), "YES" )When i try the OR solution i get the following error
I was actually able to get this to work with the solution you provided.
However, i am now trying to filter to just the codes that have "Yes" next to them. When i look at your file i see that when i put the "Not identified" calculated measure into the values it doesn't show the Blank() codes. However when i do this same step it does not filter the table the same way. I actually see both the identified "Yes" codes as well as the Blank Field codes. I cannot seem to filter out these Blank() values.
Hi rhcentennialh ,
Could you please share your formula if it does not contain any confidential information, then we could locate where the issue is?
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.See the solution used below
Not identified by Outside = IF ( SELECTEDVALUE ( 'Table'[Code] ) IN SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [Internal vs Outside] = "Internal" ), "c", [Code] ), BLANK (), "YES" )However when i put this into the table it does not prefilter to just the items that have "Yes". It shows both the codes that have "Yes" and are Blank(). What i want to do now is take this table and filter to only show the codes that have "Yes". This is not possible in the Visual Level Filter from what i have been abele to do.
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.The issue wasn't with your formula it was with a linking datatable that i had to adjust.
Everything now works perfectly.
Thank you for all of the support!