Forum Discussion

SandeA's avatar
SandeA
Helper III
4 years ago
Solved

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): The table c...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

SandeA's avatar
SandeA
Helper III
4 years ago

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

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: 

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