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

In that case try:

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

Hi PaulDBrown ,

 

I tried your DAX solution (instead of mine which is inefficient by the way), and there is a slight change in the output. Before I explain let me give you a bit of context by giving you a sample dataset:

 

https://docs.google.com/spreadsheets/d/1JCT11VFbF0z3nD7Iy1OpEu0hphVFCoYN/edit?usp=share_link&ouid=117875464156957854959&rtpof=true&sd=true

 

The FILTER() in your DAX code means now I have rows for "Shift Start", "Break Start" and "Break End"  corresponding to an employee. So my final output will also have the "Employee Clock Number" of someone who clocked out of a shift, i.e., has a "Shift End" row corresponding to the clock number.

 

It's better understood when you try it one the sample data!

 

Thanks for your reply!

Midhun

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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]
    )