Forum Discussion

hansei's avatar
hansei
Helper V
6 years ago
Solved

Help with Summarize

I'm having trouble understanding summarize and wonder if someone could help me? If I have a table as:

NAME1DATE1NUM1
harry1/1/201911
harry3/1/201922
harry2/1/201933
sally6/1/2019100
sally5/1/2019200
sally4/1/2019300

 

and i would like to use summarize based on the row with the latest date

NAME1DATE2NUM2
harry3/1/201922
sally6/1/2019100

 

the first 2 columns are clear to me as,

 

NewTable = SUMMARIZE( Table ), Table[NAME1], "DATE2", LASTDATE( Table[DATE1] )

 

 

but I am dont' know how to get the corresponding number in the SUMMARIZE function

 

DAX
  • hansei's avatar
    hansei
    6 years ago

    I ended up with this for my DAX:

    T2 = SUMMARIZE( 
    Table, Table[NAME1], 
    "DATE2", LASTDATE( Table[DATE1] ), 
    "NUM2", CALCULATE( FIRSTNONBLANK( Table[NUM1], 1 ), LASTDATE( Table[DATE1] ) )
)

    wherein the temporary table returned from LASTDATE propagated filter to orginal Table and then to CALCULATE

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi hansei ,

     

    This would be my approach, I sure there are other way to achieve the same 🙂

     

    Table 2 = ADDCOLUMNS(
     SUMMARIZE('Table', 'Table'[NAME1], "LastDate", max('Table'[DATE1])), "Number", 
     var currentName1 = 'Table'[NAME1]
     return
     calculate (sum('Table'[NUM1]), 
     filter('Table', 'Table'[NAME1] = currentName1), 
     filter('Table', 'Table'[DATE1] = [LastDate])))

    The Summarize will give you table with names and last dates. 
    Then you have to calculate / collect the number value from the table based on Name and last date. There can be multiple names with the same last date (at least in theory). That is why both name and last date are used in the calculate to find the correct value. 

     

    Hope this helps

     

    jan 

     

     

    • hansei's avatar
      hansei
      Helper V
      6 years ago

      Hello Anonymous ,

       

      I'm unsure why, but your solution doesn't seem to filter on LastDate. It aggregates all row entries withe the SUM(). It does the same even when written

      filter('Table', 'Table'[NAME1] = currentName1 && 'Table'[DATE1] = [LastDate] )

      resulting in e.g. 55 for harry

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi hansei ,

         

        That surprises me, since I took your sample data and used it in an empty PBIX: 

         



         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Why don't you simply create measure which finds max date per user and filter out that visual by adding measure into visual level filter.

    Max Date =
    CALCULATE (
    MAX ( Table1[Date] ),
    ALLEXCEPT ( Table1, Table1[name] )
    )
    Filter=if(max(table1[date])= [max date],1,0)

    And set this filter measure to 1 in visual level filter.


    Thanks
    Pravin

    If it resolves your problem mark it as a solution.
    • hansei's avatar
      hansei
      Helper V
      6 years ago

      Hellow Anonymous , the measure is needed when the dataset is consumed. My solution cannot be based on visuals.

  • hansei's avatar
    hansei
    Helper V
    6 years ago

    While I know I can do this fairly easily in mashup, I thought SUMMARIZE was the way to go. I ended up with the rather inelegant solution of just adding a calculated column a la

    Num2 = 
        var vc1 = [Name2]
        var vltst = [Date2]
        var vmatches = FILTER( Table, [Name1] = vc1 && [Date1] = vltst )
        return FIRSTNONBLANK( SELECTCOLUMNS( vmatches, "whatever", [Num1] ), 1 )
  • hansei's avatar
    hansei
    Helper V
    6 years ago

    I ended up with this for my DAX:

    T2 = SUMMARIZE( 
    Table, Table[NAME1], 
    "DATE2", LASTDATE( Table[DATE1] ), 
    "NUM2", CALCULATE( FIRSTNONBLANK( Table[NUM1], 1 ), LASTDATE( Table[DATE1] ) )
)

    wherein the temporary table returned from LASTDATE propagated filter to orginal Table and then to CALCULATE