Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm hoping you all can help me once again... I have a table visual created to pull the latest size for each database. This has been working great until I realized it wasn't showing the correct values for one of the databases. There is a 4th column (FileName6) 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. This is the visual before making any changes. The highlighted database is the one that has 3 filenames within it and each filename 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:
Solved! Go to Solution.
I built a demo (column/tablenames don't match but you'll get the idea:
DBSize =
VAR LatestFileDates =
ADDCOLUMNS (
SUMMARIZE (
ServerTable,
ServerTable[Database],
ServerTable[Filename]
),
"LatestDate", CALCULATE ( MAX ( ServerTable[Date] ) )
)
VAR TableFilter =
TREATAS (
LatestFileDates,
ServerTable[Database],
ServerTable[Filename],
ServerTable[Date]
)
VAR Result =
CALCULATE (
SUM ( ServerTable[Size] ),
TableFilter
)
RETURN Result
Copes with databases having files with different dates.
A potential issue would be if a database file was present and then deleted/renamed in later snapshots.
Oh man, I feel like a fool! ugh... its been one of those weeks! Thank you so much for all your help!
Hi,
Are dates specific to files or is are you taking a daily snapshot? Eg is it possible for two files from the same database to have different dates?
Once I know that we can look to refactor the code. Certainly going to need a SUMX in there at least but I think we can simplify the rest.
Would you be able to supply a demo pbix file?
I built a demo (column/tablenames don't match but you'll get the idea:
DBSize =
VAR LatestFileDates =
ADDCOLUMNS (
SUMMARIZE (
ServerTable,
ServerTable[Database],
ServerTable[Filename]
),
"LatestDate", CALCULATE ( MAX ( ServerTable[Date] ) )
)
VAR TableFilter =
TREATAS (
LatestFileDates,
ServerTable[Database],
ServerTable[Filename],
ServerTable[Date]
)
VAR Result =
CALCULATE (
SUM ( ServerTable[Size] ),
TableFilter
)
RETURN Result
Copes with databases having files with different dates.
A potential issue would be if a database file was present and then deleted/renamed in later snapshots.
@bcdobbs , it seems to work except for some reason, its still removing the GIS_Raster from my table:
This is what I used for the measure:
That's odd. Does it appear if you remove the top 20?
Wondering if something odd with units.
Failing that are you able to send a sample set of data or pbix file with sensistibe data removed.
Interesting, it did show up after removing the top20. However, the boss only wants to see the top 20 (top 10 in some visuals). So if I remove the TOPN filter, is there a way to add the TOPN function into the measure?
Various ways it could be done but I think there must be something odd going on in the background.
Can you switch to the data table view and filter just to show GIS_Raster, or better yet send a spreadsheet over with your data in (removing anything sensitive). Suspect still missing something.
Well, it is storing data on a daily basis for each of the filenames/databases. If I figured out google drive correctly, you should be able to get the pbix at this link: https://drive.google.com/file/d/116WnAXl-GbGvcakgpqYxs0EXs-quHbgS/view?usp=sharing
the first two sheets are how it was when I realized the issue. The 3rd one has the table from your measure.
Thank you so much for all your help!
I think it's an easy fix.
Your top N filter was using DBSize6 rather than the new DBSize6_New measure. On the version I downloaded switching them over caused it to work correctly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |