Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Hoping someone can help give me some advice on this one. I have done some research but can't quite work out the correct way to use LastNonBlank. I have two tables, one that lists products and when work has been done with them, and another table that is going to be used as a dataset for a report.
Table 1 contains some key dates I need based on a product ID however that product ID can appear multiple times.
Example of what this table looks like below:
Product ID | DateCreated | DateCompleted |
54179 | 28/07/2022 | 17/08/2022 |
711133 | 31/07/2022 | 17/08/2022 |
801371 | 01/08/2022 | 19/08/2022 |
801371 | 01/08/2022 | 23/08/2022 |
711133 | 01/08/2022 | |
801371 | 03/08/2022 | 24/08/2022 |
102333 | 06/08/2022 | 14/08/2022 |
102333 | 06/08/2022 | 14/08/2022 |
704112 | 10/08/2022 | 15/08/2022 |
What I need is to pull out the last date where something was completed for this product. Other dates for each product can be ignored so I think LASTNONBLANK should give this to me but I need to select the last completion date for each unique product ID.
In my second table I have the unique Product IDs and I want to eventually get the last date that something was completed added to that table as a lookup. What I want to end up with is something like the below:
Product ID | DateCompleted |
54179 | 17/08/2022 |
711133 | 17/08/2022 |
801371 | 19/08/2022 |
102333 | 14/08/2022 |
704112 | 15/08/2022 |
Is anyone able to help me with this?
Thank you in advance for your help!
Solved! Go to Solution.
Hi @Alex_Bartlett ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Measure =
CALCULATE (
MAX ( 'Table'[DateCompleted] ),
FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[DateCompleted] ) ) )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @Alex_Bartlett ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Measure =
CALCULATE (
MAX ( 'Table'[DateCompleted] ),
FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[DateCompleted] ) ) )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
@Alex_Bartlett , In case you need a new table
Summarize(filter(Table, not(isblank(Table[Date])) ), [Product ID] , "DateCompleted", max(Table[DateCompleted]) )
or a measure
maxx(filter(Table, not(isblank(Table[Date])) ), Table[DateCompleted])
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |