GROUPBY in a CALCULATE/MAX measure

[SandeA]

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:

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: 

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!

[SandeA]

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

[bcdobbs]

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?

[bcdobbs · 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.

[SandeA]

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

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

[bcdobbs]

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.

[SandeA]

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? 

[bcdobbs]

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.

[SandeA]

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!

[bcdobbs]

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.