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.
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!