Need a leading zero on a Month with DirectQuery

[Jaxidian]

So I have a table that looks like this:

ID int

[Value] decimal(29,9)
DateForSearching datetime
YearNumber int

MonthNumber int

DayNumber int

HourNumber int

I want to do a bunch of visualizations on this data and I need the ability to group them by year-month, year-month-day, and year-month-day-hour. Ideally, those would be represented as (respectively): "2016-12", "2016-12-25", and "2016-12-25 23:00". I can mostly get this done with some new calculated columns (Year-Month = MyTable[YearNumber] & "-" & MyTable[MonthNumber]) except for the leading zero scenarios for something like "2017-01" ends up being "2017-1" (I need the leading zeroes for month, day, and hour).

How can I get these leading zeroes from either my integer values or from the datetime value? I just can't find anything that works with DirectQuery. 😞

[JimKingPowerBI]

I believe that the best solution is the following one that was provided by ebalcaen on 05/31/18.

This is much easier, and will work in all situations:

We add a zero to the month formula by using the concatenation operator (&). Then we use the Right formula to pick only the first two digits from the right.

Year-Month with Trailing Zero on Month:

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[ebalcaen]

This is much easier, and will work in all situations:

We add a zero to the month formula by using the concatenation operator (&). Then we use the Right formula to pick only the first two digits from the right.

Year-Month with Trailing Zero on Month:

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[fex09]

This is the one that worked for me.

Year-Month = Year([DateColumn]) & "-" & Right("0" & Month([DateColumn]),2) 

[parry2k]

 You should able to do it by using if condition

Year-Month = 'Calendar'[Year] & "-" & if('Calendar'[Month Number] < 10, "0", BLANK()) & 'Calendar'[Month Number] 

[Jaxidian]

It tells me I cannot use an IF in a DirectQuery report.

[parry2k]

I just used the formula on direct query.

What is your data source for direct query? Are you using Live COnnection?

[Jaxidian]

It's direct-to-Azure SQL Database (no caching/syncing).

I thought "DirectQuery" was synonymous with a "live connection". If that's untrue then my terminology is mixed up.

[v-huizhn-msft · Accepted solution]

Hi @Jaxidian,

First, you should click File -> Options and then Settings -> Options -> DirectQuery, then selecting the option "Allow unrestricted measures in DirectQuery mode" shown in following screenshot. When that option is selected, you can create calculated column and measures.



As I tested, If function can be used in DirectQuery Model. I reproduce your scenario(connect to SQL Server database) and get the expected result. Create a column using th following formula, please see the result in screenshot below.

Year-month = IF('HumanResources vEmployeeDepartment'[Month]<=10,CONCATENATE('HumanResources vEmployeeDepartment'[Year],CONCATENATE("-0",'HumanResources vEmployeeDepartment'[Month])),CONCATENATE('HumanResources vEmployeeDepartment'[Year],CONCATENATE("-",'HumanResources vEmployeeDepartment'[Month])))

Please ckeck if you invoke creating calculated columns and measures as the solution above. If you have any question, please let me know.

Best Regards,
Angelia

[Jaxidian]

That solved the problem! And I just deployed the pbix as a Power BI Embedded report and there were no issues with that kind of deployment, either. Thanks for the help!!