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.

[d_gosbell]

There is one obvious syntax error.

Instead of: 

DEFINE
TABLE

Your query should start with: 

DEFINE
VAR

[masplin]

Still get an error "cannot identify table that contains [AdvisorsonCall] column which is in the TableVar created in the VAR section? 

This syntax creates an answer and doesn't error in PBD.

The workbook is too big to post and I can't slim it down as dont have access to reduce the size of the queries at the moment.

[d_gosbell]

---

@masplin wrote:

Still get an error "cannot identify table that contains [AdvisorsonCall] column which is in the TableVar created in the VAR section? 

---

There are 2 replies here, what syntax did you try? In your first post you called the column AdvisersOnCall with an "e" in Advisers is this a simple typo?

This syntax creates an answer and doesn't error in PBD.

What syntax works? Can you post it here?

---

The workbook is too big to post and I can't slim it down as dont have access to reduce the size of the queries at the moment.

---

We don't need the real data, just 5-10 rows from each table and just the columns used in each table would be enough. We just need enough to see the structure of your data.

[masplin]

Yes sorry fat finger i'm using the doce posted originally all with an "e"

This code creates a measure, but I dont like the outcome . Can,t attach an image of the output table for some reason.  This measure works out the max for each timeKey (minutes) within each qtr hr for each day. It then works out the average of that value for each minute over the days, and the total is the average of those averages.  What I wanted was  each day subtotlal to be the MAX and the total over the days to be the average of the days

Measure = 
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
AVERAGEX(QtrMaX,[QTRMAX])           

So I wanted to use DAX studio to show me the table created in the second VAR QtrMax

I've since solved it by breaking it into 2 measures creating the max of each qtr each day then seperately doing the 2nd step. still like to know if i could have done it in one measure.

I'll try and post the images, not asking me for location so odd box requiring source

[masplin]

sorry late reply, but that still doesnt work in DAX studio.  Must be that DAX studio handles the ADDCOLUMNS column differnetly to Power BI?  Seems wierd.

[d_gosbell]

---

@masplin wrote:

Must be that DAX studio handles the ADDCOLUMNS column differnetly to Power BI?  Seems wierd.

---

No it definitely doesn't. I'm actually the author of DAX Studio so I can 100% confirm that we do no special handling of any DAX functions, we just sent the query through directly to the data engine. What ever error you are getting - you should get the identical error message if you paste the query into a new calculated table in your model.

[masplin]

Sorry i'm being extremely stupid as never created a new table in code before.  I pasted the same query into a new table in the modle and get "Syntax for EVALUATE is incorrect"

I pasted this

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])
           )
EVALUATE QtrMaX    

[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.

[masplin · Accepted solution]

That's perfect thanks. all makes sense

Much appreciated

Mike

[masplin]

Seems you have to be signed in to add images

So what i wanted was for the numbers at the 10:00 subtotal line to read  10 (maxof 27/8), 12 (max of 28/8), 11 (average of these 2)

Date table is on the right with date rerlated To a date table and timekey related to a time table. 

[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 ) ) )

[v-frfei-msft]

Hi @masplin ,

Please use SUMMARIZECOLUMNS to have a try. If it doesn't work, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.