Validate transiction in a history table

[Anonymous]

Hello

I have an history table that has all the status change from an item like this:

id itemid previous present index

1  1         A             B           1

2  1         B             C           2

3  1         C             D           3

4  2         A             B           1

5  2         F              G           2

What I need is to remove from any calculation the itemid 2 because it did not had the correct flow (A to B, F to G) making a column that gives False for every itemid=2 entry.

How can I do it?

Thanks

[Ashish_Mathur]

Hi @Anonymous,

This works

=IF([ID]=1,"OK",IF(LOOKUPVALUE([Present],[Index],CALCULATE(MAX([Index]),FILTER(Data,Data[ItemID]=EARLIER(Data[ItemID])&&Data[Index]<EARLIER(Data[Index]))),Data[ItemID],Data[ItemID])=[Previous],"OK","Discard"))

[Anonymous]

Hello @Ashish_Mathur

Tried your formula and in fact signals the bad transictions but does not invalidate the item as a whole, maybe I need to had a second column as suggested prior?

[Ashish_Mathur]

Hi, My formula does show Discard against ItemID 2. That is what you wanted.

[Anonymous]

Using your formula I get this:

As you can see same issueid different outcome.

[Phil_Seamark · Accepted solution]

Sorry, I mistyped the data into the dataset I was using to build your code.

Please try these two calculated columns

Bad Row = 
VAR currentValue = 'Table1'[previous]
VAR previousValue = CALCULATE(
                            MAX('Table1'[Present]),
                            FILTER(
                                ALL('Table1'),
                                'Table1'[itemid] = EARLIER('Table1'[itemid]) &&
                                'Table1'[index] = EARLIER('Table1'[index])-1
                                )
                                )
RETURN IF(previousValue <> currentValue && previousValue <> BLANK(), 1,0)    
                        

and finally

Bad Item = CALCULATE(
                    MAX('Table1'[Bad Row]),
                    ALL(Table1),
                    'Table1'[itemid] = EARLIER('Table1'[itemid])
                    )

[Anonymous]

@Phil_Seamark, I used your formulas in the calculated columns and they are working.

Now I'm having problems with the filtering, because they are appearing in the table. This must be due to relationships between the tables.

I will try t work on that,

[Phil_Seamark]

Hi @Anonymous

One way to achieve this in DAX is to add two calculated columns to your data.  The first flags individual rows as having issues

Bad Row = 
VAR currentValue = 'Table1'[itemid]
VAR previousValue = CALCULATE(
                            MAX('Table1'[previous]),
                            FILTER(
                                ALL('Table1'),
                                'Table1'[id] = EARLIER('Table1'[id]) &&
                                'Table1'[index] = EARLIER('Table1'[index])-1
                                )
                                )
RETURN IF(previousValue <> currentValue && previousValue <> BLANK(), 1,0)    
                        

While the 2nd takes the above column and marks the whole item as being invalid

Bad Item = CALCULATE(
                    MAX('Table1'[Bad Row]),
                    ALL(Table1),
                    'Table1'[id] = EARLIER('Table1'[id])
                    )

[Anonymous]

Thanks for the reply @Phil_Seamark

I'm getting an comparison error, Text and integer. previous is of type text, and issued id of type integer.

Have to check how to change it.