Identify rows with multiple values

[macfuller]

This is best explained with an example. We have a daily error file that shows vouchers that have exceptions. A single voucher line can generate multiple errors. Sometimes this is because there were multiple things wrong with it, and sometimes because a single root cause can throw multiple errors.

Certain error combinations (e.g. P400 and E110) are known to be a result of a single cause. A P400 can also occur on its own. I would like to be able to identify such rows and route them for resolution. In the example below vouchers 456 and 789 would be routed for resolution.

I am assuming that a calculated column is the best way to do this but I'm getting confused on the DAX I would enter into Test Column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Voucher[/TD]
[TD]Error[/TD]
[TD]Test Column[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]R500[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]P400[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]E110[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]P400[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]E110[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]P400[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]E110[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]


I had initially though of a FILTER construct, but this logic would also return any value where there might be more than the P400 and E110 errors as well as the E110 errors alone. (I would separately filter by P400 in the pivot table so the other voucher lines that returned 0 because they didn't have any P400 errors.)

=IF (
    CALCULATE (
        COUNTROWS ( 'Match Exception' ),
        FILTER (
            'Match Exception',
            'Match Exception'[ME_Key] = EARLIER ( 'Match Exception'[ME_Key] )
                && ( 'Match Exception'[Rule] = "P400"
                || 'Match Exception'[Rule] = "E110" )
        )
    ) < 2,
    "Y",
    "N"
)

I'm not sure if I need some sort of CONCATENATEX to check which errors were returned. Your help is appreciated!

 

[Matt Allington]

Maybe I don’t get the usecase, but why not create a lookup table of the error codes, and add a category column to that table such as “send to be fixed” or whatever. Then you can just filter on that column.

 

[macfuller]

Ummmm. I think with your suggestion, since it's a lookup table, it would be 1:1 with the error code? Thus if I used a slicer to pick P400 and E110 I'd get those, but I wouldn't know if the E110 also had a P400 associated with it. Nor would I know if the P400 had an R500 or some other error associated with it that made it ineligible for the P400 fix.

I came up with the following code that appears to give me the first step in what I wanted - a list of the error codes associated with the voucher line. I can then filter by combinations of interest (P400, P400+E110, E110 + P400). I think I'd need another calculated column if I wanted a boolean indicator as to whether this particular voucher was eligible for a P400 fix in order to save the filtering.

=CALCULATE (
    CONCATENATEX (
        DISTINCT ( 'Match Exception' ),
        'Match Exception'[Rule],
        ","
    ),
    FILTER (
        'Match Exception',
        'Match Exception'[ME_Key] = EARLIER ( 'Match Exception'[ME_Key] )
            && 'Match Exception'[ME Match Status] = EARLIER ( 'Match Exception'[ME Match Status] )
    )
)

 

[macfuller]

This code appears to work, though it takes a while to update... I would prefer something where I didn't have to enumerate all the possible combinations and create something that was rules-based instead, but on to the next report I guess.

=
VAR ErrorList =
    CALCULATE (
        CONCATENATEX (
            DISTINCT ( 'Match Exception'[Rule] ),
            'Match Exception'[Rule],
            ",",
            'Match Exception'[Rule], ASC
        ),
        FILTER (
            'Match Exception',
            'Match Exception'[ME_Key] = EARLIER ( 'Match Exception'[ME_Key] )
                && 'Match Exception'[ME Match Status]
                    = EARLIER ( 'Match Exception'[ME Match Status] )
        )
    )
RETURN
    SWITCH (
        ErrorList,
        "P400", "Simple Pricing Change",
        "E110,P400", "Simple Pricing Change",
        "E110", "Incorrect Price Calculation",
        "R500", "Simple Receiving",
        "V500", "Simple Voucher",
        "R650,V500", "Revise UoM",
        -- and so on --<and so="" on...="">,
        ErrorList
    )

</and>