Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😄
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |