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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
SandeA
Helper III
Helper III

Using MAX to get most recent data

Hi all, its me again! I'm trying to pull the most current data from a table to use in a gauge visual. The table contains data like this (this is a very small portion of the table):

SandeA_0-1658422268079.png

The table contains multiple filenames so I'm trying to create a measure that will pull the most current size for a specific file name. I've got this part of the measure created:

 

FilenameDB6Size = CALCULATE (SELECTEDVALUE ( Database6[Size6] ), FILTER (Database6Database6[Date6] = MAX (Database6[Date6] ) ))

but the value returned is "blank". I'm not sure if that is because I don't have it narrowed down to one specific file name or if there is a problem with the measure. 

 

I'd like to add a filter in there to only produce a value if the filename =" filename" (in this table example, that would be "ReportServer"). 

 

Thank you in advance for your help!

1 ACCEPTED SOLUTION

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 [FilenameDB6Sizeas 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 )
    )

yingyinr_1-1658823528206.png

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

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

14 REPLIES 14
SandeA
Helper III
Helper III

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

SandeA_0-1658499842679.png

Using the first database (AcctsPay) as a further example, when sorted by the database name, this is what shows:

SandeA_1-1658500479575.png

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 [FilenameDB6Sizeas 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 )
    )

yingyinr_1-1658823528206.png

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

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.

@v-yiruan-msft 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.

SandeA_0-1659539234732.png

the GIS_Raster should be a total value of 125,209 by adding these 3 filename sizes together:

SandeA_1-1659539351408.png

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])
)
RETURN
     CALCULATE (
          MAX ( Server6[Size6] ),
          FILTER ( Server6, Server6[Date6] = _maxdate6 )
)
The results ended up removing GIS_Raster completely from the Top 20: 
SandeA_2-1659539502184.png

Any help you can provide would be awesome! Thank you!

parry2k
Super User
Super User

@SandeA maybe your requirement is not clear or I'm missing it. Better to send a sample pbix file with the expected output. Share it using one/google drive, and remove sensitive information before sharing.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@SandeA try this measure, change the column and table name as per your model.

 

Top Size = 
SUMX ( 
    Files, 
    VAR __file = Files[FileName]
    RETURN
    CALCULATE (
        SUM ( Files[Size] ),
        KEEPFILTERS(
            TOPN (
                1,
                FILTER ( ALL ( Files ), Files[FileName] =  __file ),
                Files[Date],
                DESC
            )
        )
    )
)

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

nope, still not correct, sorry. It now is showing a value of 577k.  I don't think I want to use the TOPN because the current value isn't always the largest value. The sizes fluxuate. That is why I'm trying to get just the value for the most current day in the table. There is a job that updates the this table daily. 

SandeA
Helper III
Helper III

@claymcooper that's strange, I got the same results with yours as I did with the other one. Its still showing 551K instead of 198K 

@SandeA what happens if you put a filter on the page and select only that file name? Just curious

parry2k
Super User
Super User

@claymcooper this will give wrong information in the row context of Date6 column. FYI

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

claymcooper
Resolver II
Resolver II

Change it to 
FilenameDB6Size = CALCULATE (SUM(SIZE6), Database6[Date6] = MAX (Database6[Date6] )

Additionally you could filter the data in Power Query to pull in only the most recent date if you didn't need the historical data.

on another note, (pardon my ignorance here), I hear alot about Power Query, but have no idea how to access it. I've looked all over my desktop version of Power BI and can't see anything other than the Filters pane

 

Click the Transform Data button in the top ribbon. That opens Power Query

parry2k
Super User
Super User

@SandeA try this measure:

 

Most Size = CALCULATE ( SUM ( YourTable[SizeColumn] ), TOPN ( 1, ALL ( YourTable), YourTable[DateColumns], DESC ) )

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k that did pull data, but not the correct amount.  Based on the table, the Ferns filename should have a value of 198,327

SandeA_1-1658429487681.png

 

but the value showing in a card from the measure is showing: 

SandeA_2-1658429574729.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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