March 12, 2016 at 1:59 pm
I have a working PBIX file that looks at symptom data over time, and I was trying to do the whole thing inside of PowerBI Desktop, without using SQL Server (heresy, I know!), just to see if it could be done. The original data I received is in Excel (I have 2013), and I can do everything I need to except generate a calendar table. If I use SQL Server, I can just call a function that generates the Calendar for me... I just pass in the startDate and the number of Days I want the calendar to span and I'm off to the races.
When I try to create the Calendar using the CALENDAR() function from this link, I get
"Expression error: The name 'CALENDAR' wasn't recognized. Make sure it's spelled correctly."
I have product version 2.32.4307.641, if it matters. Does the function not work with PowerBI?
March 12, 2016 at 9:03 pm
pietlinden (3/12/2016)
I have a working PBIX file that looks at symptom data over time, and I was trying to do the whole thing inside of PowerBI Desktop, without using SQL Server (heresy, I know!), just to see if it could be done. The original data I received is in Excel (I have 2013), and I can do everything I need to except generate a calendar table. If I use SQL Server, I can just call a function that generates the Calendar for me... I just pass in the startDate and the number of Days I want the calendar to span and I'm off to the races.When I try to create the Calendar using the CALENDAR() function from this link, I get
"Expression error: The name 'CALENDAR' wasn't recognized. Make sure it's spelled correctly."
I have product version 2.32.4307.641, if it matters. Does the function not work with PowerBI?
That particular Calendar() function is a DAX function available in SSAS (Analysis Services) and, I could be wrong, but I don't believe it will work with PowerBI.
This is what I'll sometimes use. I don't remember when I first created it. Other times, I may use a Calendar Table. A lot of folks use similar functions. And, no, I don't know if this works with PowerBI, either.
As usual, details and usage examples are in the comments.
CREATE FUNCTION dbo.Dates
/**********************************************************************************************************************
Purpose:
Given a start and end date, create all dates between them inclusively.
Usage Examples:
--===== Basic Syntax
SELECT Date FROM dbo.Dates(@pStartDate,@pEndDate)
;
--===== All Dates for Year 2016
SELECT Date FROM dbo.Dates('20160101','20161231')
;
--===== All Dates from 1900-01-01 thru 2200-01-01
SELECT Date FROM dbo.Dates('1900','2200')
;
Revision History:
Rev 00 - Date Unknown - Jeff Moden - Initial Creation and Unit Test
**********************************************************************************************************************/
(
@pStartDate DATE
,@pEndDate DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))v(N)) --=== 10E01 or up to 10 Rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --=== 10E04 or up to 10,000 Rows
,E16(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c, E4 d) --=== 10E16 or more rows than you'll ever need
SELECT Date = @pStartDate
UNION ALL
SELECT TOP (DATEDIFF(dd,@pStartDate,@pEndDate))
Date = DATEADD(dd,CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT),@pStartDate)
FROM E16
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2016 at 7:58 am
This should work, I have models using this so I wonder why it doesn't work in your case, your version certainly supports it.
😎
BTW: I normally use a similar function as Jeff because it is supported in wider range of products.
March 13, 2016 at 8:03 am
Eirikur Eiriksson (3/13/2016)
This should work, I have models using this so I wonder why it doesn't work in your case, your version certainly supports it.😎
BTW: I normally use a similar function as Jeff because it is supported in wider range of products.
Could it be that someone simply didn't enable or maybe didn't install SSAS components?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2016 at 10:41 am
That someone would be me =).
No, I didn't install SSAS. I was just trying to see how much I could do in PowerPivot without using T-SQL. When I did the whole thing with T-SQL, I used (I think) a calendar table function from here, and since i could specify the start and end dates, I had no excess dates... Was just trying to get my head around M, but that might not be worth the hassle.
March 13, 2016 at 1:28 pm
found an explanation for any other intrepid folks that might be trying to create a calendar using PowerQuery... (not sure why one would want to do that, since it's so easy to use a script here... but anyway. Maybe because you don't have access to one?)
There's an explanation in PowerPivot Alchemy on page 197 that is missing from Rob Collie and Avichal Singh's book. Collie's book has some code, but the really confusing thing is that he uses Date as a variable name and as a datatype. If you read the explanation in Power Pivot Alchemy, they put some of the script in italics so that you can see what values to change. (he admits to being something of an M script kiddie, which is okay... a little bit of explanation of what's going on would have been a lot more helpful... otherwise, really good book!)
Hope this helps someone else... just a really confusing chunk of code for me.
March 13, 2016 at 1:39 pm
Are you using it as a measure/calculate table?
😎
March 13, 2016 at 3:36 pm
No, I'm using the Calendar table as a Dimension so I can lump sales etc into "bins" -- summarize by Year/Month whatever.
I was using a dataset someone gave me that was about heart-related symptoms over time. The basic structure of the table was
CREATE TABLE PatientData (
PatientID INT,
SymptomName VARCHAR(20),
SeverityLevel TINYINT,
ConsentDate DATE );
I originally used someone's code from here (Todd Fifield's, I think) that created a Calendar table... I basically did something like this:
DECLARE @MinDate DATE
, @MaxDate DATE
, @DayCount INT;
SELECT @MinDate = MIN(Consent_Date)
, @MaxDate = MAX(Consent_Date);
SET @DayCount = DATEDIFF(day,@MinDate,@MaxDate) + 1;
and then I passed @MinDate and @DayCount to the function that returns a Calendar table. (with Monthname, dayOfWeek, etc...)
I was just trying to see how hard it would be to create a calendar table like that on the fly in M. In a word, PITA. Might be worth it for intellectual exercise, but otherwise, just a hassle. =(
March 13, 2016 at 4:11 pm
pietlinden (3/13/2016)
No, I'm using the Calendar table as a Dimension so I can lump sales etc into "bins" -- summarize by Year/Month whatever.
While that works and can be fairly well effective, I find that a totally non-intuitive and somewhat unorthodox method for lumping report items together works very well without giving up much in the line of flexibility. Rather than trying to do your binning by joining, I've found that it's much more effective to add persisted computed columns to the source table with the formula to calculate the 1st day of the month, week, quarter, or year. It's a bit like using a materialized (indexed) view but without that little bit of heartache. And, those columns can be indexed! In fact they can be indexed to include other columns so that a trip to the table isn't even necessary until after the aggregation is complete and you just need to add some "labels" to it. An added benefit is that since the indexes would have the temporally based columns as the leading column, they would rarely require any defragmentation.
Of course, since monthly sales don't change after they've been entered, perhaps a preaggregated table containing older aggregations unioned with a query of current data would also speed up the process.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2016 at 4:31 pm
Jeff,
that's basically what I did. (it's like a mini star schema, and Calendar is just a dimension table). Turns out, I used your calendar table-valued function. =) ... so I have a bunch of stuff in the table that's persisted so that it's easy to report on.
The interesting part was that PowerBI let me declare some variables, get the earliest and latest dates in my table, and generate the Calendar on the fly... but creating the table in M was waaay more than I wanted to do. I did read Chris Webb's blog and some other stuff, but seems like a huge hassle... Might try it again when I get my head around M a little better.
Pieter
March 14, 2016 at 12:26 am
I think you are missing the Calculated Table. If you go to the Data View, Modeling, New Table and enter
DIM_DATE = CALENDAR(DATE(2016,1,1),DATE(2016,12,31)) it will produce a date table for the period entered. You can then reference this date set in your syntax/model. What you cannot do is referencing the function as an object within the model.
😎
March 14, 2016 at 12:51 pm
Thanks Eirikur! Learned something new today!
Kinda wish you could choose which columns you wanted in the table, but not possible, I don't think. Super handy for when you have to find gaps etc, though.
In my dataset, the patterns I found were:
1. different symptoms spike on different days. (Mondays and Fridays)
2. symptoms spike in August...
3. Weird spike around the 5th day of the month
The point being that I wouldn't have seen that without a Calendar table. =)
Just wondering, though... the CALENDAR(MinDate,MaxDate) function seems to return something like a table-valued function with a single column, so you can't add columns to it. As such, it's pretty much worthless, unless all you want to group on is a single date. If I try to add columns, like DayOfWeek, I get two records (start date and end date) instead of the span of records like I had before.
Is there something about a Calendar table that I don't understand?
... rereading the fine print " What you cannot do is referencing the function as an object within the model." ... Might just be me, but that pretty much makes what would be an incredibly useful function completely worthless. A date dimension table is super handy if and only if you can add lots of columns that you can summarize on, like MonthName, DayName, etc. And since you can't do that with this, it's not terribly useful. Drat!
Pieter
Thanks!
Pieter
March 17, 2016 at 3:09 pm
I got this to work...
I started with a dataset with a date column, Consent_Date.
Created a new table and entered this:
NewDateTable = CALENDAR(MINX('PatientSymptoms',[CONSENT_DATE]),MAXX('PatientSymptoms',[CONSENT_DATE]))
... which generated a table of all the dates between the earliest and the latest date in 'PatientSymptoms'.
Then I could just add a bunch of calculated columns to get all the various slicer values I wanted. (MonthNumber, etc).
I guess the part I didn't understand at first is that CALENDAR() is like a TVF in SQL Server... returns a table, not a scalar value. =)
--- actually, the CALENDAR() function returns a hierarchy with a few members... you can later add your own to fill the hierarchy out... I'm still thinking I need a SQL to DAX encoder/decoder ring...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply