Forum Discussion
Dynamic Data Segmentation
FreemanZ yes, Dax Patterns is SQLBI, though not their static segementation as I really rather have it be dynamic because I'm dealing with time.
Apologies on no sample data initially, had to run out the door to ferry a child to practice and frustrated.
Here is what I'm dealing with:
'Reports' table:
| Report.ID | Report Title |
| 01 | Name1 |
| 02 | Name2 |
| 03 | Name3 |
| 04 | Name4 |
'Report Creation' table (related to Reports table by Report.ID):
| Run.ID | Report.ID | Date |
01 | 02 | xx/mm/yyyy |
| 02 | 02 | xx/mm/yyyy |
| 03 | 01 | xx/mm/yyyy |
| 04 | 03 | xx/mm/yyyy |
| 05 | 01 | xx/mm/yyyy |
| 06 | 02 | xx/mm/yyyy |
| 07 | 03 | xx/mm/yyyy |
| 08 | 03 | xx/mm/yyyy |
| 09 | 01 | xx/mm/yyyy |
| 10 | 02 | xx/mm/yyyy |
There is also a Date Table, set-up properly.
I've defined the following segement table:
'Run Segements':
| SegementName | Min | Max |
| Low | 0 | 10 |
| Mid | 10 | 75 |
| High | 75 | 1,000,000 |
I'd prefer to have the High Max as 'null' but all of the examples use finite segements, so I just went with a number high enough that it would never be reached.
I have a measure:
'Count Report Runs' =: COUNT('Report Creation'[Run.ID]) + 0
A measure was required because there are some reports which have never been run (hence the analysis effort!) so I need to be able to return a 0 (since you can't count rows that don't exsist).
Using my sample data, we can see that we get the following count results for each report
| Report | Count | 'fake' count (See note below) |
| Name1 | 3 | 23 |
| Name2 | 4 | 78 |
| Name3 | 3 | 8 |
| Name4 | 0 | 0 |
Now, let's pretend we have a lot more data, and it looks like the 3rd column.
In which case what I would like to be able to return is a visual that illustrates this:
| Report | Segment Name |
| Name1 | Mid |
| Name2 | High |
| Name3 | Low |
| Name4 | Low |
Or a matrix that might look like this (extrapolating run data):
| Report | CY20 | CY21 | CY22 |
| Name1 | Low | Mid | Mid |
| Name2 | High | Mid | High |
| Name3 | Low | Mid | Low |
| Name4 | Mid | Low | Low |
hi rpiboy_1
try like:
FreemanZ thanks, that seems to work in a limited manner, now I'll have to compare it to what I was trying to do so I can understand it.
However, I can't seem to filter the table visual showing Report Name & Segement name so that I'm only looking at a list of Reports that are in the mid segement for instance... Any thoughts?
Thanks!