Forum Discussion
Dynamic Data Segmentation
First off, I've reviewed the various posts by RADACAD, Enterprise DNA and SQLBI on this topic.
I have measures based on RADACAD & SQLBI all working, but its not giving me what I need/want.
I don't know why I can't wrap my head around this. All I need is basic catorgization of my data. All of the examples are based upon financial or performance analysis. I just need to be able to filter a list to say 'here are all the rows that meet the criteria of X segement'.
Specifically I have data that reprsents how often PDFs are generated from various Report Tempalates; frequency of creation.
I have measure [Count Runs] that calculates how often each report has been run over time.
I just want to dynamically drop each report (row) into a bucket to segement the data based on how often each report was run.
Can anyone point me to existing documention that may aid me? If not I can post a sample data model, etc. but was hoping to avoid that.
#FrustratingFriday
5 Replies
hi rpiboy_1
have you checked these:
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
p.s posting sample data with expected result is always suggested.
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
