Re: Dynamic Data Segmentation

rpiboy_1 · ‎01-20-2023

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

FreemanZ · ‎01-20-2023

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.

rpiboy_1 · ‎01-20-2023

@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

FreemanZ · ‎01-20-2023

hi @rpiboy_1

try like:

Measure =

VAR _count = ['Count Report Runs']

RETURN

MINX(

FILTER(

RunSegments,

RunSegments[Min]<=_count

&&RunSegments[Max]>=_count

),

RunSegments[SegmentName]

)

rpiboy_1 · ‎01-21-2023

@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!