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!