Forum Discussion
Using MAX to get most recent data
Hi SandeA ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of measure [FilenameDB6Size] as below:
FilenameDB6Size = VAR _selfilename = SELECTEDVALUE ( Database6[FileName6] ) VAR _maxdate6 = CALCULATE ( MAX ( Database6[Date6] ), FILTER ( ALLSELECTED( Database6) , Database6[FileName6] = _selfilename ) ) RETURN CALCULATE ( MAX ( Database6[Size6] ), FILTER ( Database6, Database6[Date6] = _maxdate6 ) )If the above one can't help you get the desired result, please provide more 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
Ok, I'll try to explain a little better. I have tables for 2 servers that contain database names and the size of that database on a given day. These tables are updated every day with that day's size of the database. This is a small sample of what is in the table as of yesterday (sorted desc by date):
Using the first database (AcctsPay) as a further example, when sorted by the database name, this is what shows:
As you can see, when the data was first tracked, the size was larger. On some of the databases, that number has gone up and down and back up again throughout the last year.
I am trying to create either a card or gauge visual for each of the databases and a chart/graph for all of them so we can quickly see which are getting too high. Most of the measures I'm playing with are doing a SUM of the size per database. I'm trying to only pull whatever the latest value is. In the top table that would be the value for 7/21/2022, tomorrow it would be the value for 7/22/2022, etc.
Does that make more sense? I'm not sure how to use google / one drive to share files, sorry
Hi SandeA ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of measure [FilenameDB6Size] as below:
FilenameDB6Size =
VAR _selfilename =
SELECTEDVALUE ( Database6[FileName6] )
VAR _maxdate6 =
CALCULATE (
MAX ( Database6[Date6] ),
FILTER ( ALLSELECTED( Database6) , Database6[FileName6] = _selfilename )
)
RETURN
CALCULATE (
MAX ( Database6[Size6] ),
FILTER ( Database6, Database6[Date6] = _maxdate6 )
)
If the above one can't help you get the desired result, please provide more 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
Anonymous I'm hoping you can help me once again... What you provided has worked great! However, there is a 4th column (FileName) in the table that I hadn't been using because I was told it wasn't needed. Well, I found out that some of the databases (Database6) have multiple files(FileName6) and the results were only pulling the largest sized file for the database, causing the total size to be wrong. I think I just need a GROUPBY function added. This is the visual before making any changes. The highlighted database is the one that has 3 filenames within it and each has its own value.
the GIS_Raster should be a total value of 125,209 by adding these 3 filename sizes together:
So I tried changing the code to this with the GROUPBY added:
DB6Size = VAR DBSize6 =SELECTEDVALUE ( Server6[FileName6])VAR _maxdate6 =CALCULATE (MAX ( Server6[Date6] ),FILTER ( ALLSELECTED( Server6) , Server6[FileName6] = DBSize6 ),GROUPBY(Server6,Server6[Database6]))RETURNCALCULATE (MAX ( Server6[Size6] ),FILTER ( Server6, Server6[Date6] = _maxdate6 ))The results ended up removing GIS_Raster completely from the Top 20:Any help you can provide would be awesome! Thank you!