Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]) )
Solved! Go to 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 ) ) )
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.
There is one obvious syntax error.
Instead of:
DEFINE TABLE
Your query should start with:
DEFINE VAR
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.
@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.
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
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.
@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.
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
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.
That's perfect thanks. all makes sense
Much appreciated
Mike
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.
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 ) ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |