Forum Discussion

rpiboy_1's avatar
rpiboy_1
Helper V
3 years ago

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 ...
Help!
  • rpiboy_1's avatar
    rpiboy_1
    Helper V
    3 years ago

    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.IDReport Title
    01Name1
    02Name2
    03Name3
    04Name4

     

    'Report Creation' table (related to Reports table by Report.ID):

    Run.IDReport.IDDate

    01

    		02xx/mm/yyyy
    0202xx/mm/yyyy
    0301xx/mm/yyyy
    0403xx/mm/yyyy
    0501

    xx/mm/yyyy

    0602

    xx/mm/yyyy

    0703

    xx/mm/yyyy

    0803

    xx/mm/yyyy

    0901

    xx/mm/yyyy

    1002

    xx/mm/yyyy

     

    There is also a Date Table, set-up properly.

     

    I've defined the following segement table:

    'Run Segements':

    SegementNameMinMax
    Low010
    Mid1075
    High751,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

     

    ReportCount'fake' count (See note below)
    Name1323
    Name2478
    Name338
    Name400

    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:

    ReportSegment Name
    Name1Mid
    Name2High
    Name3Low
    Name4Low

     

    Or a matrix that might look like this (extrapolating run data):

    ReportCY20CY21CY22
    Name1LowMidMid
    Name2HighMidHigh
    Name3LowMidLow
    Name4MidLowLow
    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      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's avatar
        rpiboy_1
        Helper V
        3 years ago

        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!