Forum Discussion
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 | |||||
| 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!
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
4 Replies
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 QinAmazing! You're a legend Anonymous !!! Thank you so much for this
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.
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.
