Forum Discussion
Solved
Need a leading zero on a Month with DirectQuery
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 visua...
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
Looks like I can't login to my old account 😞 But this should work with nulls and as an alternative to the solution below with the IF statement.
Year-Month = Year([DateColumn]) & switch(Month([DateColumn]),1,"-01",2,"-02",3,"-03",4,"-04",5,"-05",6,"-06",7,"-07",8,"-08",9,"-09",10,"-10",11,"-11",12,"-12")
Oh yes! IF solved my problem.
dateSolvedYearMonth =
IF( ISBLANK(AllRequests[dateSolvedLocalSE]), BLANK(),
Year(AllRequests[dateSolvedLocalSE]) & "-" & Right("0" & Month(AllRequests[dateSolvedLocalSE]),2)
)
Thank you again!
Best regards,
Fredrik