Forum Discussion

Fepatrat's avatar
Fepatrat
New Member
1 year ago

Matrix Visual Filtering

Hello guys, 

I am trying for the last 3 days to figure our how to have the total column in decimal duration (which I did), yet, i don't want to have the decimal duration for each interval, and I am very unlikely to make it happen anytime soon. Can someone help out in this matter? thank you! 

 

3 Replies

  • MarkLaf's avatar
    MarkLaf
    Super User
    1 year ago

    Are your durations datetime with "hh:mm:ss" format? And is "Interaction Duration" in your matrix a measure?

     

    If yes, to both, then the below should evaluate wherever [Interaction Duration] evaluates. If not, please provide additional info re: data types, model, current related DAX in use, and provide some simple dummy data that reproduces your problem.

     

    Decimal Duration = 
VAR secs = 
    DATEDIFF( 
        DATEVALUE( "00:00:00" ), 
        [Interaction Duration],
        SECOND 
    ) 
VAR intEquiv = 
    60 -- 1 min = 1.0
    // 60 * 60 -- 1 hr = 1.0
    // 24 * 60 * 60 -- 1 day = 1.0
RETURN
INT( secs / intEquiv )
+ MOD( secs, intEquiv ) / intEquiv
    • Fepatrat's avatar
      Fepatrat
      New Member
      1 year ago

      Yes, the durations are hhmmss format, the problem is that I only need the total column duration as decimal, not for each interval that I have (from 12:30 to 00:00). I am using the following measures for this visual 

       

       

       

       

       

       

      #Adjusted_Interaction_duration_hhmmss =
      VAR avg_sec = AVERAGE(df_dazn_AHT_tmp[Adjusted_Interaction_Duration_s])
      VAR hh = INT(avg_sec / 3600)
      VAR mm = INT(MOD(avg_sec, 3600) / 60)
      VAR ss = INT(MOD(avg_sec, 60))
      RETURN
      TIME(hh,mm,ss)
      //FORMAT(TIME(hh, mm, ss), "hh:mm:ss"))
       
      #Total_min_decimal_adjusted =
      IF(
          NOT ISINSCOPE(df_dazn_AHT_tmp[Interval]),
          VAR AvgDuration = AVERAGE(df_dazn_AHT_tmp[Adjusted_Interaction_Duration_s])
          RETURN IF(
              ISBLANK(AvgDuration),
              BLANK(),
              AvgDuration / 60
          ),
          BLANK()
      )

      I am trying to hide the column for each interaction but as far as I got I only made the values to be blank..not to enterily delete the columns 

      Thank you so much! 
      • MarkLaf's avatar
        MarkLaf
        Super User
        1 year ago

        I understand now. You have all the components you need. Just replace the BLANK() in the ISINSCOPE condition with your hh:mm:ss measure. Or put it in a third measure (and to visualize what I mean) :

         

        Adjusted_detail-hhmmss_total-decimal = 
IF(
    ISINSCOPE( df_dazn_AHT[Interval] ),
    [Adjusted_Interaction_duration_hhmmss],
    [Total_min_decimal_adjusted]
)