Latest values from table

[munnaz]

Hi All, New here and to powerBi and this seems to more difficult that first imagined. 

I have some inspection and replacement tables that i would like to return the latest relavent data to.

INSPECTION TABLE
InspectionID	ASSETID	COMPONENTID	Measurement	DateOccured	Comments
1	A	1294	50	1/01/2020
2	A	1295	100	1/01/2020
3	A	1294	55	5/02/2020
4	A	1295	105	5/02/2020
5	A	1294	60	6/03/2020	NEEDS REPLACE
6	A	1295	110	6/03/2020
7	A	1555	50	6/03/2020	NEW
8	A	1555	51	8/04/2020	GOOD
9	A	1295	111	8/04/2020
10	B	1661	5	8/04/2020
11	B	1661	6	9/05/2020
12	B	1661	11	11/06/2020	replace next
13	B	1841	1	12/06/2020
14	C	5	10	8/04/2020	All ok
15	C	5	10	8/05/2020
16	C	5	11	11/06/2020
17	C	6	22	8/04/2020
18	C	6	22	8/05/2020	All good
19	C	6	22	11/06/2020

COMPONENTID
ASSET ID	COMPONENTID	INSTALLED DATE	Name
A	1294	1/01/2020	Impeller
A	1295	1/01/2020	Nozzle
A	1555	6/03/2020	Impeller
B	1661	8/04/2020	Impeller
B	1841	12/06/2020	Impeller
C	5	8/04/2020	Seal
C	6	8/04/2020	Seal

Where the component table has a 1 to many relationship to the inspection table.

I would like to find the latest measurements, latest dates installed and last comments.

My expected results are as follows:

Last installed
ASSET ID	Name	Date
A	Impeller	6/03/2020
A	Nozzle	1/01/2020
B	Impeller	12/06/2020
C	Seal	8/04/2020
C	Nozzle	8/04/2020

Last Comment
ASSET ID	name	Date	Comment
A	Impeller	8/04/2020	GOOD
A	Nozzle
B	Impeller	12/06/2020	REPLACED ON DAY
C	Seal	8/04/2020	All ok
C	Nozzle	8/05/2020	All good

Last measurement	Measurement	DateOccured
A	Impeller	51	8/04/2020
A	Nozzle	111	8/04/2020
B	Impeller	1	12/06/2020
C	Seal	11	11/06/2020
C	Nozzle	22	11/06/2020

Any help is really appreciated!

[Anonymous · Accepted solution]

Hi @munnaz ,

According to my understand , you want to display the lastest installed date ,the lastest but not blank comment and the latest measurement, right?

You could use the following formula after doing "Merge" .  Here is the pbix file.

1.The latest installed

LastInstalled =
CALCULATE (
    MAX ( 'COMPONENTID'[INSTALLED DATE] ),
    ALLEXCEPT ( COMPONENTID, COMPONENTID[ASSET ID] )
)

2.The latest comment

lastComment =
VAR hasBlank =
    IF ( MAX ( 'Merge1'[Comments] ) = BLANK (), 0, 1 )
VAR sumBlank =
    SUMX (
        FILTER (
            ALL ( 'Merge1' ),
            [ASSETID] = MAX ( 'Merge1'[ASSETID] )
                && [Name] = MAX ( 'Merge1'[Name] )
        ),
        hasBlank
    )
VAR _lastComment =
    MAXX (
        FILTER (
            ALL ( Merge1 ),
            [ASSETID] = SELECTEDVALUE ( Merge1[ASSETID] )
                && [Name] = SELECTEDVALUE ( Merge1[Name] )
                && NOT ( ISBLANK ( Merge1[Comments] ) )
                && [DateOccured]
                    = CALCULATE (
                        MAX ( Merge1[DateOccured] ),
                        FILTER (
                            ALL ( 'Merge1' ),
                            [ASSETID] = SELECTEDVALUE ( Merge1[ASSETID] )
                                && [Name] = SELECTEDVALUE ( Merge1[Name] )
                                && [Comments] <> ""
                        )
                    )
        ),
        [Comments]
    )
RETURN
    IF ( sumBlank = 0, BLANK (), _lastComment )

3.Apply this measure to filter (set as "1") for last measurement.

rank =
RANKX (
    FILTER (
        ALL ( Merge1 ),
        'Merge1'[ASSETID] = MAX ( 'Merge1'[ASSETID] )
            && 'Merge1'[Name] = MAX ( 'Merge1'[Name] )
    ),
    CALCULATE ( MAX ( ( 'Merge1'[DateOccured] ) ) ),
    ,
    DESC
)

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[munnaz]

Amazing! You're a legend @Anonymous !!! Thank you so much for this

[Greg_Deckler]

@munnaz See if Lookup Min/Max works for you. Basically you lookup the latest date/index/whatever and then use that to return the value you want.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

@ me in replies if you still need help.

[munnaz]

@Greg_Deckler  thanks for the reply mate! But i am still unsure on how to proceed especially since i have a combination of 2 or more values to give me a result.