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
Anonymous
Not applicable

Only displaying latest row in dependence on a date

 

Hey,

 

I am currently looking for a solution to only exhibit the latest rows of the following table in the reporting view for all the unique values in the column CAD-NR. (the original data is already filters by slicers):

 

Dates.JPG

 So the expected result should be (rows with red line should be erased):

 

result.JPG

 I have already tried with Top N filter, but did not succeed. Do you have any precious suggestions for me? 🙂

Thanks in advance,

 

Volki

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Sorry, miss something. Try this:

Measure =
VAR latestdate_ =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[CAD-NR] ) )
RETURN
    IF ( MAX ( 'Table'[Date] ) = latestdate_, 1 )

m.JPG 

 

Best Regards,

Icey

 

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

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Try this:

latest.jpg

 

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

Hey, this works fine if I just have the date.

 

If I pull the column "type" in, the table exhibits again all values.

 

If I choose last for the columne type as well, I get the right amount of rows with the right dates, but the wrong values for type ....

Is there a way to get trhis right?

rcharara
Regular Visitor

Did you try to create a new table :

 

GroupEnCADNR = SUMMARIZE('Table','Table'[CAD-NR.],"Datum",MAX('Table'[Datum]), "Typ",MIN('Table'[Typ]))
 
Latest row in dependence.png
amitchandak
Super User
Super User

@Anonymous , Try there two measures in place of datnum and typ


Datenum new = max(Table[Datenum])
Typ new= lastnonblankvalue(Table[Datenum], max(Table[Typ]))

Anonymous
Not applicable

Hi,

 

thx for your seuggestion.

 

Did u take into account, that I need the latest value for each CAD-value?

I am asking because the measures do not contain the CAD-Column.

 

BR

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Sorry, miss something. Try this:

Measure =
VAR latestdate_ =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[CAD-NR] ) )
RETURN
    IF ( MAX ( 'Table'[Date] ) = latestdate_, 1 )

m.JPG 

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

 

Hey @Icey ,

 

sorry, I called out victory too quickly.

 

I have tested some othrt cases, and it seems to not worked as expected. Please have a look on the picture attached. Do u know why it could be the case? (Some CAD-Values are simply not shown in your measure or in the table)

BR

Test.png

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

It works well in my example attached above.

no type.JPG

 

Or, you can try to create a measure like so:

Measure 2 =
VAR latestdate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[CAD-NR] = MAX ( 'Table'[CAD-NR] ) )
    )
RETURN
    IF ( MAX ( 'Table'[Date] ) = latestdate_, 1 )

 

If this doesn't work either, please share me your .pbix file for test. Please remove sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.

 

 

Best regards

Icey

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Anonymous
Not applicable

Hey @Icey ,

 

thank you, it worked!

 

Can u tell me if it is possible to show all results in the table and apply results with your formula only when a button is clicked?

 

Also it would be interesting to show not only the latest but the rows that correspond to a certain number of last dates, e.g. the last three dates.

 

What could be a way to achieve that?

 

Thx

 

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.