Multi IF function

[Anonymous]

Hi,

I have a list of id's, list of approval type and list of date / time against each approval type and id. 

Refer sample table below - 

Id     Approval type     Date/Time

x             a                 8/7/2015 6:44:00 PM

x             a                 8/7/2015 5:31:00 PM

x             b                 6/7/2015 5:31:00 PM 

y             a                 11/19/2015 4:36:00 PM

y             b                 11/25/2015 6:43:00 PM

y             b                 11/24/2015 8:41:00 AM

z             a                  12/4/2015 7:22:00 PM

I need to group these by ID, then approval type and then take only the latest apporoval date / time.

I am able to group them and create another table, but can someone explain how can i just filter on the latest date/time for each id and each record type within the id.

Thanks!

[v-huizhn-msft · Accepted solution]

Hi @Anonymous

You’d better create a another table using SUMMARIZE Function. In the function, filter the latest date/time based on id and each record type. I try to reproduce your scenario as follows, please review.

Please click the new table(highlighted in yellow), then type the following formula. Run it , and you get expected result like the screenshot below. In the formula, the “Test” table is the same as your given sample data.

Table 2 = SUMMARIZE(Test,Test[Id],Test[Approval type],"Date",MAX(Test[Date/Time]))

 

If you have any question, please feel free to ask.

Best Regards,
Angelia

[Greg_Deckler]

I believe that you want to create a measure like so:

Max Date/Time = MAX([Date/Time])

Use a matrix visualization and put Id, Approval Type and this measure into the matrix.

Not sure what this has to do with the title of the post.