Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rhcentennialh
Helper II
Helper II

Find Distinct Data - Based On Two Columns

I have a database where I would like to identify unique codes that a particular person has not identified or coded. Below is an example of my problem.

 

Problem: I would like to exclude all of the codes "Name A" has identified/coded as well as the other "Names" for those identify codes, please see the data example below. 

 

Excluding just "Name A" codes is easy however i also want to remove the "Names" associated with Code 3 & Code 18 since they are also related to code "Name A" has identifed/captured.

 

NameCode
Name G1
Name C1
Name G2
Name F2
Name B2
Name G3
Name F3
Name A3
Name A4
Name A5
Name A6
Name A7
Name B8
Name B9
Name F10
Name B10
Name F11
Name G12
Name A13
Name D14
Name G15
Name D15
Name C16
Name A17
Name G18
Name A18
Name B18
Name H19
Name G19
2 ACCEPTED SOLUTIONS

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"
)

15.PNG

 

 

 

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.

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.

View solution in original post

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.

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.

View solution in original post

20 REPLIES 20
v-lid-msft
Community Support
Community Support

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

6.PNG

 

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"
    )

7.PNG

 

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 ()
)

8.PNG

 

 

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.

 

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.

 

1.PNG2.PNG

 

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.

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 NameCode
Student 1Name G1
Student 1Name C1
Student 1Name G2
Student 1Name F2
Student 1Name B2
Student 1Name G3
Student 1Name F3
Student 1Name A3
Student 1Name A4
Student 1Name A5
Student 1Name A6
Student 1Name A7
Student 1Name B8
Student 2Name B9
Student 2Name F10
Student 2Name B10
Student 2Name F11
Student 2Name G12
Student 2Name A13
Student 2Name D14
Student 2Name G15
Student 2Name D15
Student 2Name C16
Student 2Name A17
Student 2Name G18
Student 2Name A18
Student 2Name B18
Student 2Name H19
Student 2Name G19

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"
)

1.PNG2.PNG

 

 

 

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.

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 OutsideStudentCode
InternalStudent 11
OutsideStudent 11
OutsideStudent 12
InternalStudent 13
OutsideStudent 14
OutsideStudent 15
OutsideStudent 16
OutsideStudent 17
InternalStudent 18
OutsideStudent 18
OutsideStudent 19

 

Expected Result

Internal vs OutsideStudentCode
OutsideStudent 12
OutsideStudent 14
OutsideStudent 15
OutsideStudent 16
OutsideStudent 17
OutsideStudent 19

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.

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.

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.

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

 

Error.JPG

 

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.

 

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.

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!

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"
)

15.PNG

 

 

 

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.