cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User VI
Super User VI

Re: Checking if right code was deleted

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

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Checking if right code was deleted

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

Highlighted
Super User VI
Super User VI

Re: Checking if right code was deleted

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Checking if right code was deleted

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

Highlighted
Helper IV
Helper IV

Re: Checking if right code was deleted

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

Highlighted
Super User VI
Super User VI

Re: Checking if right code was deleted

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Checking if right code was deleted

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors