How can I create a function in PowerBI using DirectQuery

Jørgen Wulff
Posts: 3
Joined: Tue May 16, 2017 10:26 pm

How can I create a function in PowerBI using DirectQuery

Postby Jørgen Wulff » Tue May 16, 2017 10:36 pm

I have an extract from an SQL Database, that originally was done in PowerPivot, but now our company will like to move on to PowerBI.

I will be extracting data as a DirectQuery, as I want the size of the file to be very small.

I have transferred most of the data, so I can extract data and I have created most of my measures and new columns, which has worked fine.

But I have one big issue: in PowerPivot I have a custom column in my DimCalendar, where the formula goes like this:

Mm1 = LOOKUPVALUE([MonthName],[YearMonthNumber],[YearMonthNumber]-1)

where YearMonthNumber is a column that just counts numbers from the start of DimCalendar. That means the J  January 2008 is month 1, and January 2009 is month 13, and so forth.

Therefore in February 2008, I would like to return the MonthName (January) based on this formula.

And in February 2009 I want to return the MonthName (January) again, as monthnumber will be 14 - 1 = 13 and the MonthName is January again.

I have seen, that I can do this as a function, but it seems like everytime I try to create this in DirectQuery, my PowerBi tries to convert the file, so I will not have a DirectQuery any longer.

Can I create a LookupValue like this in DirectQuery mode of PowerBI, or how do I get around the problem?

Best regards

Jørgen

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How can I create a function in PowerBI using DirectQuery

Postby MattAllington » Sun May 21, 2017 4:06 pm

I believe you cannot write calc columns when using direct query mode. This seems to have been fixed with the latest version of sql server but this may not help you. https://docs.microsoft.com/en-us/sql/an ... -ssas-2016
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Jørgen Wulff
Posts: 3
Joined: Tue May 16, 2017 10:26 pm

Re: How can I create a function in PowerBI using DirectQuery

Postby Jørgen Wulff » Mon May 29, 2017 7:28 pm

Hi Matt,

That might be exactly the problem, that I face. Unfortunately I do not know much about the SQL Server, so I will try to follow up on that part first. And hopefully that can be solved and I can continue with our project.

best regards

Jørgen


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest