cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Latest values from table

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     
InspectionIDASSETIDCOMPONENTIDMeasurementDateOccuredComments
1A1294501/01/2020 
2A12951001/01/2020 
3A1294555/02/2020 
4A12951055/02/2020 
5A1294606/03/2020NEEDS REPLACE
6A12951106/03/2020 
7A1555506/03/2020NEW
8A1555518/04/2020GOOD
9A12951118/04/2020 
10B166158/04/2020 
11B166169/05/2020 
12B16611111/06/2020replace next
13B1841112/06/2020 
14C5108/04/2020All ok
15C5108/05/2020 
16C51111/06/2020 
17C6228/04/2020 
18C6228/05/2020All good
19C62211/06/2020 

 

 

COMPONENTID  
ASSET IDCOMPONENTIDINSTALLED DATEName
A12941/01/2020Impeller
A12951/01/2020Nozzle
A15556/03/2020Impeller
B16618/04/2020Impeller
B184112/06/2020Impeller
C58/04/2020Seal
C68/04/2020Seal

 

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 IDNameDate
AImpeller6/03/2020
ANozzle1/01/2020
BImpeller12/06/2020
CSeal8/04/2020
CNozzle8/04/2020

 

 

Last Comment  
ASSET IDnameDateComment
AImpeller8/04/2020GOOD
ANozzle  
BImpeller12/06/2020REPLACED ON DAY
CSeal8/04/2020All ok
CNozzle8/05/2020

All good

 

 

Last measurementMeasurementDateOccured
AImpeller518/04/2020
ANozzle1118/04/2020
BImpeller112/06/2020
CSeal1111/06/2020
CNozzle2211/06/2020

 

Any help is really appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Latest values from table

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:

9.24.6.1.PNG

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Latest values from table

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Latest values from table

@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. 

Highlighted
Microsoft
Microsoft

Re: Latest values from table

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:

9.24.6.1.PNG

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

View solution in original post

Highlighted
Frequent Visitor

Re: Latest values from table

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors