What is the best practice to show rows by latest date, grouped by two additional columns?

[Anonymous]

Hi all,

I have a table (tbl_Fact) with the following structure/contents:

SOURCE          STATUS          LOAD DATE     ROW COUNT

Office              Successful      12/03/2018        88
Office              Successful      13/03/2018        112

Warehouse      Failed             22/03/2018        2

Office              Failed              28/03/2018       110

Warehouse      Failed             14/03/20018      22

I would like to be able to show in a table visualisation the rows with the latest LOAD DATE, grouped by SOURCE and STATUS. So from the above dataset I would like to return the following subset:

SOURCE          STATUS          LOAD DATE     ROW COUNT
Office              Successful       13/03/2018       112

Office              Failed              28/03/2018       110

Warehouse      Failed             14/03/20018      22

What is the best practice to achieve this?

Thanks in advance.

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

To achieve your requirement, you can try following method:

1. Create a measure with following expression:

Last Date =
VAR Last =
    CALCULATE (
        MAX ( tbl_Fact[LOAD DATE] ),
        ALLEXCEPT ( tbl_Fact, tbl_Fact[SOURCE], tbl_Fact[STATUS] )
    )
RETURN
    IF ( MAX ( tbl_Fact[LOAD DATE] ) = Last, 1, 0 )

2. Put this measure in Visual level filters and set the filter to equal to 1.

 

Thanks,
Xi Jin.

[Anonymous]

ssobti - It wasn't what I was looking for but thanks for the effort in responding.

v-xjiin-msft - This is exactly what I am after. Thank you for your help, and apologies for the delay in providing gratitude.

[Anonymous]

Hi @Anonymous,

Right click on Load Date under Fields and click on Latest, it will then show only the latest date.

Does this help?