Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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])
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 😄
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |