Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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):
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:
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!
Solved! Go to 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 [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
@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.
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:
Any help you can provide would be awesome! Thank you!
@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.
@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
)
)
)
)
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.
@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
@claymcooper this will give wrong information in the row context of Date6 column. FYI
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.
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
@SandeA try this measure:
Most Size = CALCULATE ( SUM ( YourTable[SizeColumn] ), TOPN ( 1, ALL ( YourTable), YourTable[DateColumns], DESC ) )
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
but the value showing in a card from the measure is showing:
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |