Need to compare two columns, return results as custom column

[grggmrtn]

Hi! Hope you all can help a little with a slightly complicated situation:

My table [Services] is divided up in [Person] (unique id), [Category], and [DaysList], which lists every day that the person received a service in the [Category].

Person	Category	DaysList
1	One		01-01-2019
1	One		02-01-2019
1	Two		01-01-2019
1	Two		02-01-2019
1	Two		03-01-2019
2	One		01-02-2019
2	One		02-02-2019
2	One		03-02-2019
2	Two		02-02-2019
2	Two		03-02-2019
2	Two		04-02-2019
2	Two		05-02-2019
3	One		01-01-2019
3	One		02-01-2019
3	One		03-01-2019
3	Two		02-01-2019
3	Two		03-01-2019

[DaysList] is type date.

I need to verify the quality of our data. In theory, [DaysList] should have a match in each [Category] value, per [Person]. So, [Category]"One" should have a date, and [Category]"Two" should have the same date for each [Person]

So I need to create a control column and two new similar columns:

[Match] which just returns true/false if there is a [DaysList] match between [Category] values, per [Person]

[DaysNoMatch] which tells me which [DaysList] per [Category] per [Person] doesn't match.

[CategoryNoMatch] which tells me which [Category] per [Person] doesn't match.

The resulting table should look like this:

Person	Category	DaysList	Match	CategoryNoMatch	DaysNoMatch
1	One		01-01-2019	True		
1	One		02-01-2019	True		
1	Two		01-01-2019	True		
1	Two		02-01-2019	True		
1	Two		03-01-2019	False	Two		03-01-2019
2	One		01-02-2019	False	One		01-02-2019
2	One		02-02-2019	True		
2	One		03-02-2019	True		
2	Two		02-02-2019	True		
2	Two		03-02-2019	True		
2	Two		04-02-2019	False	Two		04-02-2019
2	Two		05-02-2019	False	Two		04-02-2019
3	One		01-01-2019	False	One		01-03-2019
3	One		02-01-2019	True		
3	One		03-01-2019	True		
3	Two		02-01-2019	True		
3	Two		03-01-2019	True		

I really hope this made sense, and that someone knows exactly what I should do 🙂

[AlB · Accepted solution]

Hi @grggmrtn,

Try the following for your new calculated columns in DAX. See it all at work in the attached file. I believe there are a couple of mistakes in the expected results you show for 'DaysNoMatch' (otherwise I have misunderstood the logic).

Match =
IF (
    COUNTX (
        FILTER (
            Table1;
            Table1[Person] = EARLIER ( Table1[Person] ) && Table1[DaysList] = EARLIER ( Table1[DaysList] )
        );
        Table1[Category]
    ) = 2;
    TRUE ();
    FALSE ()
)

CategoryNoMatch = IF(NOT Table1[Match];Table1[Category])

DaysNoMatch = IF(NOT Table1[Match];Table1[DaysList])

[grggmrtn]

@AlBYou work miracles 🙂 That's exactly what I needed, and it works perfectly in my report.

It's good that you spotted my mistakes in the expected results - it was way too early when I wrote 😄