Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX code for today's table data

Hi all,   I have the following DAX code:   Calculated table = VAR __Filter = CALCULATETABLE ( VALUES ( 'Logged Clockings'[Employee Clock Number] ), 'Logged Clockings'[Touch Description] = "Sh...
Need Help
Show and Tell
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Thanks for the sample data. See if this has better performance:

     

    Today Table =
VAR _SE =
    CALCULATETABLE (
        VALUES ( 'Logged Clockings'[Employee Clock Number] ),
        FILTER (
            'Logged Clockings',
            'Logged Clockings'[Touch Description] = "Shift End"
                && 'Logged Clockings'[Date] = TODAY ()
        )
    )
VAR _ACN =
    CALCULATETABLE (
        VALUES ( 'Logged Clockings'[Employee Clock Number] ),
        FILTER ( 'Logged Clockings', 'Logged Clockings'[Date] = TODAY () )
    )
VAR _Net =
    EXCEPT ( _ACN, _SE )
RETURN
    SUMMARIZE (
        FILTER (
            'Logged Clockings',
            'Logged Clockings'[Date] = TODAY ()
                && 'Logged Clockings'[Employee Clock Number] IN _Net
        ),
        'Logged Clockings'[Date],
        'Logged Clockings'[Employee Clock Number]
    )

     

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Try:

Calculated table =
SUMMARIZE (
    FILTER (
        'Logged Clockings',
        'Logged Clockings'[Touch Description] <> "Shift End"
    ),
    'Logged Clockings'[Date],
    'Logged Clockings'[Employee Clock Number]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Paul, 

     

    Thanks for replying. What I meant was:

    The CALCULATETABLE function refers to value in 'Logged Clockings' and I only want the rows that correspond to today's date from Logged Clockings.

     

    I have a solution but I don't think it's the most efficient. I created another calculated column:

    Today =
    FILTER('Logged Clockings', 'Logged Clockings'[Date] = TODAY())
     
    And then my final CALCULATETABLE refers to 'Today' table, as follows:
    Live Clocked In =
    VAR __Filter =
        CALCULATETABLE (
            VALUES ( 'Today'[Employee Clock Number] ),
            'Today'[Touch Description] = "Shift End"
        )
    VAR __Result =
        FILTER (
            SUMMARIZECOLUMNS (
                'Today'[Date],
                'Today'[Employee Clock Number]
            ),
            NOT ( 'Today'[Employee Clock Number] ) IN __Filter
        )
    RETURN
        __Result
     
    Thanks!