Forum Discussion

rhcentennialh's avatar
rhcentennialh
Helper II
6 years ago
Solved

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
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

20 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.

     

    • rhcentennialh's avatar
      rhcentennialh
      Helper II
      6 years ago

      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"

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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.