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

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

Reply
SandeA
Helper III
Helper III

GROUPBY in a CALCULATE/MAX measure

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:

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
Do I need to add another function (SUM/SUMX?) after the groupby to get this to work properly? Also, I have another server I'm doing this with that has 3 databases with the same issue, so I would need a solution that is not specific to the GIS_Raster filename. 
Any help is greatly appreciated!
1 ACCEPTED SOLUTION

I built a demo (column/tablenames don't match but you'll get the idea:

Example PBIX 

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

9 REPLIES 9
SandeA
Helper III
Helper III

Oh man, I feel like a fool! ugh... its been one of those weeks! Thank you so much for all your help!

 

bcdobbs
Super User
Super User

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?

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

I built a demo (column/tablenames don't match but you'll get the idea:

Example PBIX 

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@bcdobbs , it seems to work except for some reason, its still removing the GIS_Raster from my table:

SandeA_1-1659627140713.png

This is what I used for the measure:

DBSizes6 =
    VAR LatestFileDates6 =
        ADDCOLUMNS (
            SUMMARIZE (
                Server6,
                Server6[Database6],
                Server6[FileName6]
                ),
            "LatestDate", CALCULATE ( MAX ( Server6[Date6] ) )
            )
    VAR TableFilter =
        TREATAS (
            LatestFileDates6,
            Server6[Database6],
            Server6[FileName6],
            Server6[Date6]
            )
    VAR Result =
        CALCULATE (
            SUM ( Server6[Size6] ),
            TableFilter
        )
    RETURN Result

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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? 

SandeA_0-1659628131216.png

 

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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