Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grggmrtn
Post Patron
Post Patron

Need to compare two columns, return results as custom column

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 🙂

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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])

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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])

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.