Checking if right code was deleted

[PowerBI123456]

Hi, need some help and hopefully this makes sense. I have 2 different tables below. The first table is the data. The second table tells you the combination of codes that cannot exist together for the same ID, and the column 2 code would need to be deleted, not the column 1 code.

For example, ID A has codes 22222 and 33333. Since code 22222 is in Column 2, it was removed and code 33333 was kept.  When something is deleted, the data will be blank, but you can tell what the data is by looking at the original line item. 

However, the next example is not right. ID B has codes 44444 and 11111. But instead of code 11111 being deleted, code 44444 was deleted. 

I have 100,000s of rows I need to look through, anyone know how to do this easily in Power BI? I am just trying to see if the right code was deleted or not. 

 

[v-diye-msft]

Hi @PowerBI123456 

Not sure if my understanding is correct. 

For ID A, the data should be compared with column 2 in table2, if the data exist in column2, then it should be deleted. such as 22222

For ID B, the data should be compared with column 1 in table2,if the data exist in column1,then it should be deleted. such as 44444

If the above logic is correct, you might refer to my below measure:

Measure = 
SWITCH(SELECTEDVALUE('Table'[ID]),"A",IF(SELECTEDVALUE('Table'[Data]) in VALUES('Table (2)'[Column2]),BLANK(),MAX('Table'[Data])),"B",IF(SELECTEDVALUE('Table'[Data]) in VALUES('Table (2)'[Column1]),BLANK(),MAX('Table'[Data])
))

Pbix attached.

[PowerBI123456]

@v-diye-msft thanks! 

So the data needs to look at both columns. You want to see if an ID has the combination of codes (column & column 2), and if it does, its deleting the column 2 code, not the column 1 code. Hope that makes sense. 

[v-diye-msft]

Hi @PowerBI123456 

Do you mean if there's ID has the codes from both column 1 and column 2, then delete the one in column2?

But why you delete the 44444 in this description below? it's from column1.

" However, the next example is not right. ID B has codes 44444 and 11111. But instead of code 11111 being deleted, code 44444 was deleted. "

[PowerBI123456]

@v-diye-msft  Sorry, should have been more clear. That would be a finding/exception. I am trying to find instances where the wrong code was deleted. Hope that makes sense. Thanks!

[PowerBI123456]

Hi - just seeing if anyone had any ideas, thanks again!

[mahoneypat]

A little confused on your scenario but I think this does what you are looking for.  Assuming you have a relationship between the Data column and Column1 of your second table, you can make a Calculated Column with this expression to identify missed deletions.

Missed Deletion =
VAR __badcode =
    IF ( Codes[Item] = "Code1", RELATED ( BadCombos[Column2] ) )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( Codes ),
            ALLEXCEPT ( Codes, Codes[ID] ),
            Codes[Data] = __badcode,
            Codes[Item] <> "Code1"
        ) > 0,
        "Missed Deletion"
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PowerBI123456]

@mahoneypat thanks!! Where does your code take into account the column 1 codes, and why does code item="code 1"?

[mahoneypat]

The column 1 codes are taken into account through the relationship.  Also, I saw in the first example rows had Code1 in the first value and for the deletion, and then Code2 for the new/correct one.  I assumed (perhaps incorrectly) that it was a typo on the second set of example rows did not have 1 or 2 next to the codes.  Please confirm my assumption was incorrect, and I can propose a different measure.

Regards,

Pat

[PowerBI123456]

@mahoneypat Thanks! Forgot to mention it will be a many to many relationship if I do it that way. See below.  

[mahoneypat]

So do the data have Code1, Code2?  If not, is there some way to differentiate the intial code vs the replacement code?  DateTime column?  Or is any combination of the two codes in either order always an error?

Regards,

Pat

[PowerBI123456]

@mahoneypat no the data does not have Code 1, Code 2. I think your code is right, but I can't create a relationship since its many to many. 

[mahoneypat]

I'm assuming there is some DateTime column or something to differentiate which Code came first (one sequence is correct, the other incorrect).  If so, you can use that column.  If not, I made an Index column in the query editor on your table and was able to detect the missed deletion with this column expression.  No relationship to the other table required.

Missed Deletion =
VAR __index =
    MIN ( Codes[Index] )
VAR __badcode =
    CALCULATE (
        MIN ( BadCombos[Column2] ),
        BadCombos[Column1] = EARLIER ( Codes[Data] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( __badcode ) ),
        IF (
            CALCULATE (
                COUNTROWS ( Codes ),
                ALLEXCEPT ( Codes, Codes[ID] ),
                Codes[Index] > __index,
                Codes[Data] = __badcode
            ) > 0,
            "Missed Deletion"
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PowerBI123456]

@mahoneypat Would you be able to provide a sample file so I can take a look?

[PowerBI123456]

@mahoneypat  I want to see the index column you created. 

[mahoneypat]

I didn't make the index column with DAX.  I did it in the query editor using the button on the Add Column tab.  Here is the M code, but it is just simple index column addition.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOT0k1BNL+RZnpmXmJOUCmEQgoxeqgKnBJzUktSQUyUGSMULUagwBYgRN2s01AAEMBqtlO2M02BAGl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Item = _t, Operation = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Item", type text}, {"Operation", type text}, {"Data", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PowerBI123456]

@mahoneypat  Hi - sorry for the delayed response, but is your code taking into the account the operation? For example, I want to look at deleted.