Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

MAKE SUM() IGNORE ROW CONTEXT

Hi,   I am trying to create a measure that will calculate the sum of another calculation that converts from seconds to hours that runs per row in "activity" table and returns the total of the sum i...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Anonymous - Well it is doing a SUMMARIZE so that can take some time, See if this speeds it up:

     

    Measure 3 = 
VAR __date = MAX([Date])
VAR __table = SUMMARIZE(FILTER(ALL('Table4'),[Date]=__date),[Emp_no],[Date],"__hours",SUM([S_Total])/60/60)
RETURN
MAXX(__table,[__hours])

    As for the 2nd one, that should be equivalent. I created a second measure just like it and got the same results so not sure what is going on there. See attached, Table4, Page 3.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Greg_Deckler thanks.

 

I want to display both of the rows but show the measure only on the first row.

 

any way to do so?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Greg_Deckler , I appreciate your effort :smileyhappy:

     

    Here you have a sample data with the date that appears twice marked in green:

     

    Emp_noDateS_TotalBeginWork
    6660548601/09/2018 00:00001/09/2018 00:00
    6660548602/09/2018 00:003678002/09/2018 11:59
    6660548603/09/2018 00:002394003/09/2018 07:37
    6660548604/09/2018 00:004188004/09/2018 07:28
    6660548605/09/2018 00:004320005/09/2018 07:22
    6660548606/09/2018 00:002364006/09/2018 07:40
    6660548607/09/2018 00:00007/09/2018 00:00
    6660548608/09/2018 00:00008/09/2018 00:00
    6660548609/09/2018 00:001698009/09/2018 07:18
    6660548610/09/2018 00:00010/09/2018 00:00
    6660548611/09/2018 00:00011/09/2018 00:00
    6660548612/09/2018 00:003366012/09/2018 07:27
    6660548613/09/2018 00:002442013/09/2018 07:28
    6660548614/09/2018 00:00014/09/2018 00:00
    6660548615/09/2018 00:00015/09/2018 00:00
    6660548616/09/2018 00:003774016/09/2018 07:32
    6660548617/09/2018 00:00017/09/2018 00:00
    6660548618/09/2018 00:00018/09/2018 00:00
    6660548619/09/2018 00:00019/09/2018 00:00
    6660548620/09/2018 00:001302020/09/2018 07:27
    6660548620/09/2018 00:003600020/09/2018 20:00
    6660548621/09/2018 00:00021/09/2018 00:00
    6660548622/09/2018 00:00022/09/2018 00:00
    6660548623/09/2018 00:00023/09/2018 00:00
    6660548624/09/2018 00:003894024/09/2018 19:41
    6660548625/09/2018 00:003876025/09/2018 19:44
    6660548626/09/2018 00:003912026/09/2018 19:38
    6660548627/09/2018 00:003378027/09/2018 19:07
    6660548628/09/2018 00:00028/09/2018 00:00
    6660548629/09/2018 00:00029/09/2018 00:00
    6660548630/09/2018 00:00030/09/2018 00:00

     

     

    and here you can see the expected result with the date that appears twice marked in green:

     

    Emp_noDateHoursTotal
    6660548601/09/2018 00:00 
    6660548602/09/2018 00:0010.22
    6660548603/09/2018 00:006.65
    6660548604/09/2018 00:0011.63
    6660548605/09/2018 00:0012.00
    6660548606/09/2018 00:006.57
    6660548607/09/2018 00:00 
    6660548608/09/2018 00:00 
    6660548609/09/2018 00:004.72
    6660548610/09/2018 00:00 
    6660548611/09/2018 00:00 
    6660548612/09/2018 00:009.35
    6660548613/09/2018 00:006.78
    6660548614/09/2018 00:00 
    6660548615/09/2018 00:00 
    6660548616/09/2018 00:0010.48
    6660548617/09/2018 00:00 
    6660548618/09/2018 00:00 
    6660548619/09/2018 00:00 
    6660548620/09/2018 00:0013.62
    6660548620/09/2018 00:00 
    6660548621/09/2018 00:00 
    6660548622/09/2018 00:00 
    6660548623/09/2018 00:00 
    6660548624/09/2018 00:0010.82
    6660548625/09/2018 00:0010.77
    6660548626/09/2018 00:0010.87
    6660548627/09/2018 00:009.38
    6660548628/09/2018 00:00 
    6660548629/09/2018 00:00 
    6660548630/09/2018 00:00 

     

    thanks in advance !

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous  - I did it this way:

       

      Measure 3 = 
VAR __table = SUMMARIZE(ALL('Table4'),[Emp_no],[Date],"__hours",SUM([S_Total])/60/60)
VAR __date = MAX([Date])
RETURN
MAXX(FILTER(__table,[Date]=__date),[__hours])

      See attached, Table4, Page 3

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago


        Greg_Deckler Thanks!

         

        i have 2 more questions please :smileyhappy:

         

        1. is it a complexed measure? it's taking some time to calculate

         

        2. I had to write it exactly like you did. can you please explain to me whats the difference between the 2 syntax's :

        VAR Table1 = SUMMARIZE(ALL('activity'),[Emp_no],[Date],"DayHours",SUM([S_Total])/3600)

VAR _Date = MAX([Date])

RETURN MAXX(FILTER(Table1,[Date]=_Date),[DayHours])

        this code produces the following result just like you said in the following picture:

         

        while the following code produces a different result:

        VAR Table1 = SUMMARIZE(ALL('activity'),activity[Emp_no],activity[Date],"DayHours",SUM(activity[S_Total])/3600)

VAR _Date = MAX(activity[Date])

RETURN MAXX(FILTER(Table1,[Date]=_Date),[DayHours])

        What is the differnce?