Need Help: Recognizing inconsistent values

[Anonymous]

I'm having some issues writing Measures to analyze the below information. What I'm trying to do is create a measure that calls out inconsistencies in the "Unique Contract/Material" row's Pricing units and Units of Measure. For instance, Contract 56000 has an error with Material 44's Pricing Unit. Under this Contract's Material 44, the Pricing Unit shows as both 100 and 1. I need a measure to say, "this is wrong and needs to be checked".

Similarly, Contract 45000 has an error with Material 33's Unit of Measure. Under this Contract's Material 33, the Unit of Measure shows as both LB and Each. I need a measure that call this out as an error as well.

I greatly appreciate the help!!

Unique Contract/Material	Contract Line Number	Contract	Material	Pricing Unit	Unit of Measure
70000_55	1	70000	55	1	Each
70000_44	2	70000	44	100	LB
70000_44	3	70000	44	100	LB
56000_57	1	56000	57	100	Each
56000_44	2	56000	44	1	LB
56000_44	3	56000	44	100	LB
45000_54	1	45000	54	1000	LB
45000_33	2	45000	33	1	Each
45000_33	3	45000	33	1	LB
32000_54	1	32000	54	1000	LB
23000_33	1	23000	33	100	Each

[Greg_Deckler · Accepted solution]

Sure, seems like the easiest thing to do would be to create a column like:

Unit Defect = 
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    ALL('Table'),
                    [Unique Contract/Material] = EARLIER('Table'[Unique Contract/Material])
                ),
                "Unit of Measure",
                [Unit of Measure]
            )
        )
    )

Attached PBIX that has both defect columns.

[Anonymous]

@Greg_Deckler Thanks for the help. Can you explain what the DAX formula is doing (in long form) so that I can better understand the solution?

[Greg_Deckler]

Sure, you read it inside out.

Use ALL to remove row context and then filter that down so that your Unique Contract/Material equals your current row's value for Unique Contract/Material (EARLIER). Side note, EARLIER is perhaps the worst named DAX function in all of DAX. Now, use SELECTCOLUMNS to just select the column of interest, like Unit of Measure. Now, use DISTINCT to return the distinct (unique) values in this single column table returned from SELECTCOLUMNS. Now use COUNTROWS to count the number of rows. 

So, at the end of the day, you have the count of unqiue values within your column of interest. If it is more than 1 = bad. 🙂