Forum Discussion

jm420a's avatar
jm420a
Frequent Visitor
2 years ago

Requesting help with DAX Table Query - Get oldest date from each document Library

I am attempting to create a table that contains a distinct value for the oldest modified date in a document library, for each document library. 

Below is the DAX I am using, however, I get the error 'The expression specified in the query is not a valid table expression.' 

Any help is greatly appreciated, thank you


LastModifiedDatePerLibrary =
VAR MaxDatePerLibrary =
    ADDCOLUMNS (
        ALL ( 'AllTenantDocumentLibraries' ),  -- Replace 'YourTable' with the name of your table containing document library data
        "MaxModifiedDate",
        CALCULATE (
            MAX ( 'AllTenantDocumentLibraries'[Last Modified Date] ),  -- Replace 'Modified Date' with the actual column name for modified dates
            ALLEXCEPT ( 'AllTenantDocumentLibraries', 'AllTenantDocumentLibraries'[Document Library] )  -- Replace 'Library Name' with the actual column name for library names
        )
    )
RETURN
    MAXX ( MaxDatePerLibrary, [MaxModifiedDate] )

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    DAX Formatter seems to be ok with the query

    Is  'AllTenantDocumentLibraries' the name of an actual table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  jm420a ,

     

    You can try the following dax:

    LastModifiedDatePerLibrary =
SUMMARIZE(
    'AllTenantDocumentLibraries',
    'AllTenantDocumentLibraries'[Document Library],
    "MaxModifiedDate",
    MAXX(
        FILTER(ALL(AllTenantDocumentLibraries),
        'AllTenantDocumentLibraries'[Document Library]=EARLIER('AllTenantDocumentLibraries'[Document Library])),[Last Modified Date]))

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.