Forum Discussion

roncruiser's avatar
roncruiser
Post Patron
5 years ago
Solved

DAX: Sequential Numbering between Start and Stop Values Representing X-Axis

With DAX: Per each vREF row, I'd like to fill in the values between the start and stop Values with 1's.

 

In a pivot table the vRef row would be the Y axis of the table and the X axis would be sequentially numbered values representing time.

 

The 1's would then be summed based on how I pivot the table.  Conditional formatting added with color to reveal further information about the data

 

This is a tough one.  I don't know where to begin.  Looking for some help. 

 

Current.                             Expected                   

 

I have an example of the data here:
https://drive.google.com/file/d/1ObqyIVDtQLwuuKtnDUm6116NvMB81UT4/view?usp=sharing

Need Help

5 Replies

    • roncruiser's avatar
      roncruiser
      Post Patron
      5 years ago

      Hi Jihwan_Kim 

      With your help I'm almost there.

      I cannot use in PowerBI.  I've attempted to adapt your measure for use in Excel DAX.

       

      It's very close:

      STOP is working fine.

      STRT is all starting at 0.  Which is the issue.

       

      I overlaid what it's suppose to look like.  You can see all the STOP's are ending fine,

      but the STRT are all beginning at 0 (zero).

       

      The pivot table is in Gray gradient.

      Overlay is in purple.

       

      Here is the measure I tried to adapt for Excel DAX:

       

      =COUNTROWS(
      FILTER(
      '2D', CALCULATE(SUM('2D'[Value]), '2D'[PARAM] = "STRT") <= MAX( t_axis[t_Axis])
      && CALCULATE(SUM('2D'[Value]), '2D'[PARAM] = "STOP") >= MIN(t_axis[t_Axis])
      )
      )

       

      I've updated the Excel file:

      https://drive.google.com/file/d/1ObqyIVDtQLwuuKtnDUm6116NvMB81UT4/view?usp=sharing

       

       

      Very close!

      Thanks!

       

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    roncruiser , Create a table using generateseries

     


    time = generateseries(min(Table[Start], Max(Table[End]))

     

    then try this measure

    measure = calculate(distinctCOUNT(Table[vref]), filter(Table, max(time[value])>= table[Start] && max(time[value])<= table[end] ))

    • roncruiser's avatar
      roncruiser
      Post Patron
      5 years ago

      Hi amitchandak 

      Thanks for the timely response.

       

      Generate Series is not available in Excel for some reason.  Is there a work around for that function in Excel?

      With that said, I still have to generate this table within Excel for the pivot table ability which Power BI lacks.

       

      Excel's Pivot Table capability along with it's conditional formatting will provide me exactly what I need.

       

      Thanks Amit.

  • roncruiser's avatar
    roncruiser
    Post Patron
    5 years ago

    How do I combine the two measures below to display the gray area in between the STRT and STOP values?

    Again, cannot do this in PowerBI because the pivot table is more powerful for this specific use.

     

    File:

    https://drive.google.com/file/d/19m2hjFl5ZH1LRNigjXb8Q1f9agjrqL8D/view?usp=sharing

     

    STRT Measure

    =COUNTROWS(

    FILTER(

    '2D',

    (CALCULATE(SUM('2D'[Value]), '2D'[PARAM] = "STRT") <= MAX(t_axis[t_Axis]) ) ))

     

    STOP Measure

    =COUNTROWS(

    FILTER(

    '2D',

    (CALCULATE(SUM('2D'[Value]), '2D'[PARAM] = "STOP") >= MIN(t_axis[t_Axis]) ) ))