Newbie to DAX Studio tables as variables

[masplin]

Hi

I have never used DAX Studio before but stuggling with one of my measures that isnt giving me the result I expect so trying to breakdown what is happening. 

Firstly I tested the TableVar statement using EVALUATE and generates the table I expect which is a row for each TimeKey (which are minutes) and count of users for each day

I want to feed this table into created a second table that works out the maximum over each minute within 15 minute windows (QtrHr). i can't work out how to use a table as a variable in DAX Studio as just errors.  Please can someone tel lme the right syntax so I output the table  with rows for each day, each qtrHr and the max of the previous table?

Thanks for any assitance

Mike 

DEFINE
TABLE
TableVar =
    ADDCOLUMNS (
        SUMMARIZE ( AdvisersOnCall,AdvisersOnCall[TimeKey],'Date'[Date],'Time'[QtrHr] ),
        "AdvisersOnCall", CALCULATE ( ( COUNT ( AdvisersOnCall[participantid] ) ) )
            
    )
EVALUATE
ADDCOLUMNS(
                       SUMMARIZE(TableVar,'Date'[Date],'Time'[QtrHr],[AdvisersOnCall]),
                        "QTRMAX",       MAX( [AdvisersOnCall])
                        )

[d_gosbell · Accepted solution]

If you wanted to see the output of that second table in DAX Studio you should be able to alter it as follows to see the output of the variable at the grand total level.

DEFINE 
VAR
TableVar =
    ADDCOLUMNS (
        SUMMARIZE ( AdvisersOnCall,AdvisersOnCall[TimeKey],'Date'[Date],'Time'[QtrHr] ),
        "AdvisersOnCall", CALCULATE ( ( COUNT ( AdvisersOnCall[participantid] ) ) )
         )
        
 VAR
 QtrMaX=       
ADDCOLUMNS(
                       SUMMARIZE(TableVar,'Date'[Date],'Time'[QtrHr],[AdvisersOnCall]),
                        "QTRMAX",       MAX( [AdvisersOnCall])
           )
EVALUATE QtrMaX         

But to achieve that output you've described I would have thought that you could do something like the following:

Measure = MAXX( VALUES( 'Time'[TimeKey]), CALCULATE( COUNTROWS( AdvisersOnCall ) ) )

[d_gosbell · Accepted solution]

Sorry, that was a brain fade on my behalf. The calculated tables insert an implied EVALUATE at the top of the table expression so you can't include the explicit EVALUATE.

So you should be able to run the following in a calculated table:

Table = 

VAR
TableVar =
    ADDCOLUMNS (
        SUMMARIZE ( AdvisersOnCall,AdvisersOnCall[TimeKey],'Date'[Date],'Time'[QtrHr] ),
        "AdvisersOnCall", CALCULATE ( ( COUNT ( AdvisersOnCall[participantid] ) ) )
         )
        
 VAR
 QtrMaX=       
ADDCOLUMNS(
                       SUMMARIZE(TableVar,'Date'[Date],'Time'[QtrHr],[AdvisersOnCall]),
                        "QTRMAX",       MAX( [AdvisersOnCall])
           )
RETURN QtrMaX   

and the only difference in DAX Studio would be that you would need to explicitly add the EVALUATE at the top instead of the "Table ="

EVALUATE
VAR
TableVar =
    ADDCOLUMNS (
        SUMMARIZE ( AdvisersOnCall,AdvisersOnCall[TimeKey],'Date'[Date],'Time'[QtrHr] ),
        "AdvisersOnCall", CALCULATE ( ( COUNT ( AdvisersOnCall[participantid] ) ) )
         )
        
 VAR
 QtrMaX=       
ADDCOLUMNS(
                       SUMMARIZE(TableVar,'Date'[Date],'Time'[QtrHr],[AdvisersOnCall]),
                        "QTRMAX",       MAX( [AdvisersOnCall])
           )
RETURN QtrMaX   

The calculated table epressions are actually not as flexible as the full query syntax as they don't support the DEFINE statement.