general question about transformations in Direct Query mode

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