Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alex_Bartlett
Frequent Visitor

Last non blank help

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 IDDateCreatedDateCompleted
5417928/07/202217/08/2022
71113331/07/202217/08/2022
80137101/08/202219/08/2022
80137101/08/202223/08/2022
71113301/08/2022 
80137103/08/202224/08/2022
10233306/08/202214/08/2022
10233306/08/202214/08/2022
70411210/08/202215/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 IDDateCompleted
5417917/08/2022
71113317/08/2022
80137119/08/2022
10233314/08/2022
70411215/08/2022

 

Is anyone able to help me with this? 

Thank you in advance for your help!

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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] ) ) )
)

yingyinr_0-1661504099052.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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] ) ) )
)

yingyinr_0-1661504099052.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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])

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors