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
ChristianTD
Frequent Visitor

Show only data from the latest date

Dear all,

I have a very basic question. I constantly struggle with showing only the data with the newest date. What is the easiest way to get a visual only to show data from the most recent date.

 

As I see it there are multiple ways – a measure, filter(although this does not work for me), row count in the source.

 

I have a simple example – data set:

Date                  |type        |value|

20-06-2018          A             20

20-06-2018          B             40

19-06-2018          A             21

19-06-2018          B             39

 

And my table visual should then only show:

Date                 |type         |value|

20-06-2018          A             20

20-06-2018          B             40

 

Many thanks

\Chr

1 ACCEPTED SOLUTION

Hi @ChristianTD,

 

Seems date type values don't have a "TOP" filter type. I would suggest you try the measure below and add it to the "Visual Level filter". Don't need to add it to any visual. Then filter the filter as "1". Please give it a try.

Measure =
VAR LatestDate =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'table1' ) )
RETURN
    IF ( MIN ( 'Table1'[Date] ) = LatestDate, 1, 0 )

Best Regards,

Dale

Community Support Team _ Dale
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

28 REPLIES 28

In the accepted solution why does this work with either MIN or MAX in the IF logic . It is making my brain swim

 

Anonymous
Not applicable

Thank you so much. 
But, can we filter all visuals for the latest?

Anonymous
Not applicable

@v-jiascu-msft  Hi is there a way to do this in edit query? so that when my data is refreshed, it is much faster..

Anonymous
Not applicable

Hi @Anonymous 

 

A sample PowerQuery/M (from Query Editor) to get only latest records as below. To reduce confusion between various reserved words, I have added "Col" to all column names:

 

let
Source = Table1,
#"Grouped Rows" = Table.Group(Source, {"DateCol"}, {{"MaxDateCol", each List.Max([DateCol]), type date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"DateCol"}, Table1, {"DateCol"}, "Table1", JoinKind.Inner),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1",
{ "TypeCol", "ValueCol"},
{ "Table1.TypeCol", "Table1.ValueCol" }
)
in
#"Expanded Table1"

 

 

How to load table data by Max date by default and will changes accordingly by user's date selection

I have a table with load date and the requirement is to load data by max load date when it will load for the first time, but there is also a Filter drop down with load date and when the user will select another date , the table will load with that date.

TO achieve this I have used below measure :

Measure 2 =
var vMaxLoadDate = CALCULATE ( MAX ( EDG_COUNT_STATUS[Date] ), ALL ( 'EDG_COUNT_STATUS' ) )
return
IF(MIN(EDG_COUNT_STATUS[Date]) = vMaxLoadDate,1,0)

and set it as filter in visual filter level and set the value to it as 1. But it always load data with latest date, but when I am going to select another date from filter dropdown it does not chnage the dates in the table.

But my requirement is to load table with selected date as well as by Max load by default.

Capture.jpg

How can i achive this? Below is the screen shot for reference:

 

So by default it is loaded with 03/15/2020 which is max load date, but when ever I am going to select another date from filter values, it does load with that date.

 

 

Hi@v-jiascu-msft
Can we apply same senario for direct quirey mode. Because am a able to get such DAX like var in direct quirey mode.
Thank you
Anonymous
Not applicable

This is great, thank you much! 

Anonymous
Not applicable

And if he'd sorted desc by date and remove duplicates for ID&Date? Assuming PBI takes first occurred and drops the rest, wouldn't that be better for more ID's?

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.