general question about transformations in Direct Query mode

[lizrowden]

I am struggling to achieve the results I would like in direct query mode and continue to run into messages "This query contains transformations that cannot be used for a live connection" and "This step results in a query that is not supported in DirectQuery mode" with everything I do.  I need some advice on where to perform what functions and I would prefer to do it all in Power BI and not in the underlying SQL server query.

Data is as follows:

Customer Type	Business Unit	Call#	Equipment #	Trip#	Scheduled Date/Time	Arrive Date/Tim	Complete Date/Time	Trip Status
SERVICE	AAA	1	S101-A	1	1/11/17 8AM	1/11/17 8AM	1/11/17 11AM	COMPLETE
SERVICE	AAA	1	S101-A	2				WAITING FOR PARTS
SERVICE	AAA	1	S101-B	1	1/13/17 9AM	1/11/17 11AM	1/11/17 11:30AM	COMPLETE
SERVICE	BBB	2	S102	1	1/12/17 9AM	1/12/17 9AM		ARRIVED
SERVICE	CCC	3	S103	1	1/25/17 8AM			SCHEDULED
RETAIL	AAA	4	S104	1	1/20/17 8AM			SCHEDULED

Goal #1 – Add a filter to only show SERVICE

Whenever I try to add a filter to any field, I get the message “This step results in a query that is not supported in DirectQuery mode”. Why can’t I filter my query on any field?

Goal #2 - Create a new field that will be used to retrieve counts, sums, etc. This will be the combination of Customer Type & Business Unit. In SQL I can use a CASE statement. In Excel, I could use an IF or a VLOOKUP statement in a new column. For example…

Customer Type	Business Unit	NEW COLUMN
SERVICE	AAA	SERVICE-WEST
SERVICE	BBB	SERVICE-WEST
SERVICE	CCC	SERVICE-NORTH
RETAIL	AAA	RETAIL-WEST

I have tried to accomplish this by adding a Custom Column into the query using an IF statement and it says No syntax error detected in the Window, but after I close out I get a message that “Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly.”. Have also tried IIF. I was able to do this successfully by adding a new column in the report fields, but I am curious what I am doing wrong in the query that it is not supported.

IIF( [CustomerCode]="SERVICE" & [Entity]="AAA","SERVICE-WEST",

 [CustomerCode]="SERVICE" & [Entity]="BBB","SERVICE-WEST",

"Nothing Assigned")

Goal #3 – Remove duplicates based on specific logic

I need to have one record per call, but as you can see in my data example, I will have many records per a single call because a call can have multiple service trips against it. I need to get a count of the CALLS by STATUS based on the status of the LAST trip. In SQL query I might use the following to identify which is the Last Trip.

CASE when RANK() OVER (PARTITION BY sc.id ORDER BY isnull(st.ArrivalDateTime,99999) desc, st.id) = 1 then 1 else 0 end IsLastTrip

ZONE	STATUS	# OF CALLS
SERVICE-WEST	WAITING FOR PARTS	1
SERVICE-WEST	WAITING FOR SERVICE	2
SERVICE-NORTH	WAITING FOR SERVICE	1
RETAIL-WEST	WAITING FOR SERVICE	1

How would I determine which is the last trip in Power BI, then base my Call Counts on the status of the last trip?

[v-yuezhe-msft · Accepted solution]

Hi @lizrowden,

The issues you describe above are limitations in DirectQuery mode in Power BI Desktop. After we make changes in Query editor, we are not apply these changes to report.

For Goal #1, you can create a table visual using your fields, then create a slicer using Customer field to filter the table to only show SERVICE, or you can use visual level filter to filter your table visual.

For Goal #2, yes, you would need to create a calculated column in Report view.

For Goal #3, in “Import” mode, we can create a calculated column using RANKX() function then determine which is the last trip by evaluating if the value of the newly calculated column equals to 1, there is an example for your reference.

However, in “Directquery” mode, when creating a calculated column using RANKX() function, we will get error message as follows.


In this scenario, you would need  to input the query “RANK() OVER (PARTITION BY sc.id ORDER BY isnull(st.ArrivalDateTime,99999) desc, st.id) = 1” at the connection time, then create a calculated column named lasttrip using formula shown in the following screenshot.




Then you can base you Call Counts on the status of the last trip by creating formula like: Measure = CALCULATE(DISTINCTCOUNT(tablename[callcount]), tablename [Lasttrip]=1). 

 

Thanks,
Lydia Zhang