Get largest value from a SUMMARIZE table by id

[Anonymous]

Hi everyone, 

I'm a newbie when it comes to DAX, I'm using PowerPivot in Excel 2013,  I have a table Bugs that have all the historical data for bugs opened in our company (component, status, updated_date, rev, etc) for every change done to the bug it is recorded with a consecutive revision # 1...n.

I've been struggling trying to the get the largest rev value from a given table that I'm getting using the SUMMARIZE function as in a measure as follows so I can later SUM/SUMX the Is_Open column,  therefore, HOW can I get the largest value from the Summarize_Rev column for every id based on a date selected by the user means by a slicer in GUI?   

ie for id 2204835797  it would be 4.

NOTE: DATE(2018,08,03)   is actually dinamic based in a measure I built Date_Selected:=MIN(dtDates[DateToSelect])

 SUMMARIZE 
 (
            FILTER (Bugs,Bugs[updated_date]< DATE(2018,08,03))
            ,Bugs[id]
            ,Bugs[Is_Open]  
            ,"Summarized_Rev",MAX ( Bugs[rev] )
  ) 

id	Is_Open	Summarize_Rev
12010001801	0	21
12010001354	0	25
12010001226	0	9
12010001205	0	6
12010001151	1	3
12010001151	0	10
2204835797	0	3
2204835797	1	4
2204732202	1	9
2204732202	0	12

Please let me know if you need further details.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

If you want to calculate the Max Summarize_Rev per id, you could try with the formula below.

Measure =
CALCULATE (
    MAX ( 'Table1'[Summarize_Rev] ),
    ALLEXCEPT ( Table1, 'Table1'[id] )
)

Here is the result output.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

Thanks Cherry! 

It was not exactly what I needed surely because I wasn't clear enough :), however I was able to handle the final result I needed.

Again,  BIG thanks!