Forum Discussion
Show only data from the latest date
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
I would like not to show the new column.
I also tried with a measure Latest = LOOKUPVALUE(Table[Date];Table[Date];MAX(Table[Date])) and then adding my date to the filter and add top 1 by Latest.
Alternatively wouldn't just a simple filter work.. date top 1 by date?
Microsoft EmployeeHi 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
In the accepted solution why does this work with either MIN or MAX in the IF logic . It is making my brain swim
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?
This is great, thank you much!
- wesen
Helper I
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 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' ) )returnIF(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.
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.
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..
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"
Thank you so much.
But, can we filter all visuals for the latest?- ppatel91
I used this same measure and I applied this on visual level filter and filtered as "1", it seems to be giving me min and max date but my other visuals are not reflecting according to date. I am very new to DAX and I dont know what I need to provide after filtered to "1" ? the filter pan has "And" "Or" selection and I don't know what condition I need to provide. Is that something affecting to my visuals and they are not updating? Please help me how can I resolve this.
i had a situation, how one can get last row data of every year to compare / calculate difference?
Sample Data:
Country Name Date Price ------------ ----------- ------ Argentina 3/1/2015 3.40 Argentina 6/28/2015 3.46 Argentina 3/1/2016 3.50 Argentina 6/28/2016 3.51 Argentina 4/1/2017 3.60 Argentina 8/29/2017 3.66 Argentina 5/1/2018 3.70 Argentina 8/30/2018 3.73 Brazil 3/1/2015 2.23 Brazil 6/28/2015 2.30 Brazil 3/1/2016 2.38 Brazil 6/28/2016 2.44 Brazil 2/8/2017 2.50 Brazil 7/31/2017 2.59 Brazil 3/13/2018 2.70 Brazil 7/22/2018 2.77Output on Visual:
Country Name Date Price Difference ------------ --------- ----- ---------- Argentina 6/28/2015 3.46 Argentina 6/28/2016 3.51 0.05 Argentina 7/31/2017 3.66 0.15 Argentina 7/22/2018 3.73 0.07 Brazil 6/28/2015 2.30 Brazil 6/28/2016 2.44 0.14 Brazil 7/31/2017 2.61 0.17 Brazil 7/22/2018 2.77 0.16please help, how i can achieve this as most of the solutions are accessing Last Most when searched here and on google.
regards
allwynbazilHi v-jiascu-msft ,
I am very new to PowerBI and have tried the accepted solution above. However, I don't understand how to set the visual filter as 1. I am trying to pull the progress for the latest date. Please help me.
Regards,
Alwin
