Forum Discussion
Helper IIICalculating changes in rows over date ranges by category
This is my summarised data table for the purpose of this request (actually millions of rows, 20+categorys)
| No | Cat 1 | Cat 2 | Cat 3 | Cat 4 | Cat 5 | Cat 6 | Value | Date Start | Date End |
| 0012 | A | B | C | D | E | F | 1 | 01/01/2022 | 31/12/2030 |
| 0013 | B | B | C | D | E | F | 0 | 01/01/2022 | 31/12/2030 |
| 0014 | C | B | C | D | E | F | 0 | 01/01/2022 | 31/12/2030 |
| 0015 | D | B | C | D | E | T | 1 | 01/01/2022 | 01/12/2022 |
| 0015 | D | B | C | D | E | F | 1 | 01/12/2022 | 04/12/2022 |
When a change occurs in ANY category a new line is added for that No with a new start from date.
I want to show data in a table visualisation highlighting if there has been a change in ANY category over the dates sliced on the page.
The table visualisation can be any category.
So ignoring the value measure in the table below if the date slicer on this page was 01/01/2022 until 01/12/2022 and I wanted a table by Category 3 the table contents would be
| C | Measure | {blank} |
Blank because there is no change in any cat6 over those dates.
So ignoring the value measure in the table below if the date slicer on this page was 01/01/2022 until 04/12/2022 and I wanted a table by Category 1 the table contents would be
| A | Measure | {blank} |
| B | Measure | {blank} |
| C | Measure | {blank} |
| D | Measure | 1 |
There is a 1 in the D cat field because there is a change in any category over those dates.
Im thinking a measure counting "Date Starts" would work but cant get it to work over any category
Hope this makes sense, any help appreciated.
Hi PaulHallam ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
For slcier = CALENDAR(DATE(2022,1,1),DATE(2030,12,31))(3) We can create measures.
Measure = var _a = COUNTX(FILTER('Table',[Date Start]>=MIN('For slcier'[Date])&&[Date End]<=MAX('For slcier'[Date])),[No]) return IF(_a>=2,1)Conditional formatting = var _a = COUNTX(FILTER(ALL('Table'),MAX('For slcier'[Date])>=SELECTEDVALUE('Table'[Date End])&&MIN('For slcier'[Date])<=SELECTEDVALUE('Table'[Date Start])&&[No]=SELECTEDVALUE('Table'[No])),[No]) return IF(_a>=2,"RED")(4)Set conditional formatting for fields.
(5) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi PaulHallam ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
For slcier = CALENDAR(DATE(2022,1,1),DATE(2030,12,31))(3) We can create measures.
Measure = var _a = COUNTX(FILTER('Table',[Date Start]>=MIN('For slcier'[Date])&&[Date End]<=MAX('For slcier'[Date])),[No]) return IF(_a>=2,1)Conditional formatting = var _a = COUNTX(FILTER(ALL('Table'),MAX('For slcier'[Date])>=SELECTEDVALUE('Table'[Date End])&&MIN('For slcier'[Date])<=SELECTEDVALUE('Table'[Date Start])&&[No]=SELECTEDVALUE('Table'[No])),[No]) return IF(_a>=2,"RED")(4)Set conditional formatting for fields.
(5) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.