Maintain Direct Query with Virtual Table

[bigc]

Please excuse my lack of Power BI knowledge and terminology.

I have a report drom Dynamics 365 that uses Direct Query to get the following tables:

• Opportunity
• Opportunity Cloase
• Opportunity Product
• Email
• Appointments
• Phone Calls

I have published to Power Bi service and imported into Dynamics 365 (Sales) and a simple refresh in Dynamics updates most of the visuals in real time as expected.

My issue is where I have a union of the activity entities as below to show total activities and their status:

Activity =
var _phone = SELECTCOLUMNS('phonecall', "Activity Type", phonecall[activitytypecodename], "Owner", phonecall[owneridname], "Statecode", phonecall[statecodename], "Name", phonecall[owneridname], "Date", IF(phonecall[actualend] = BLANK(), phonecall[scheduledend], phonecall[actualend]))
var _appt = SELECTCOLUMNS('appointment', "Activity Type", appointment[activitytypecodename], "Owner", appointment[owneridname], "Statecode", appointment[statecodename], "Name", appointment[owneridname], "Date", IF(appointment[actualend] = BLANK(), appointment[scheduledend], appointment[actualend]))
var _email = SELECTCOLUMNS('email', "Activity Type", email[activitytypecodename], "Owner", email[owneridname], "Statecode", email[statecodename], "Name", email[owneridname], "Date", IF(email[actualend] = BLANK(), email[scheduledend], email[actualend]))
Return
Union(_phone, _email, _appt)

There are other columns in the below tables however all have the columns as illustrated below.

 Phone Call

actualend	scheduledend	owneridname	statecodename	activitytypecodename
10/02/2022 10:35	10/02/2022 10:30	user 1	completed	phone call
	4/04/2022 4:04	user 2	open	phone call

Email

actualend	scheduledend	owneridname	statecodename	activitytypecodename
12/04/2022 0:17		user 1	completed	email
13/04/2022 0:17		user 2	completed	email

Appointment

actualend	scheduledend	owneridname	statecodename	activitytypecodename
15/06/2022 22:00	15/06/2022 22:00	user 1	completed	appointment
	27/04/2022 4:04	user 2	open	appointment

This creates a table thats viewable in the model and I can create a visual as below which is a basic count of activitype and legend of Activity Status.

My issue is I believe this breaks the Direct Query method as it loads the table into the model and hence a refresh in Dynamics doesn't update the visual as it appears to require a refresh of the dataset.

How can I have this union and maintain Direct Query where it will be live after a visual refresh?

[amitchandak]

@bigc , better to create view in database or write a SQL using advance option

[bigc]

I have tried that with append table and it still does not refresh. I was under the understanding it would still be a direct query as can access the native sql and don't get any errors.

When doing a refresh in Power BI desktop the sync states 106 rows loaded so it's obviously loading as a model I presume.

Although the native query des look wierd as the table names seem to be underscores so am a bit confued whats happening here:

select [_].[actualend] as [actualend],
    [_].[subject] as [subject],
    [_].[description] as [description],
    [_].[statuscode] as [statuscode],
    [_].[scheduledend] as [scheduledend],
    [_].[actualstart] as [actualstart],
    [_].[statecode] as [statecode],
    [_].[activityid] as [activityid],
    [_].[owneridname] as [owneridname],
    [_].[statecodename] as [statecodename],
    [_].[statuscodename] as [statuscodename],
    [_].[activitytypecode] as [activitytypecode],
    [_].[activitytypecodename] as [activitytypecodename],
    [_].[Date] as [Date],
    [_].[t0_0] as [Date Combined]
from 
(
    (
        select [_].[actualend] as [actualend],
            [_].[subject] as [subject],
            [_].[description] as [description],
            [_].[statuscode] as [statuscode],
            [_].[scheduledend] as [scheduledend],
            [_].[actualstart] as [actualstart],
            [_].[statecode] as [statecode],
            [_].[activityid] as [activityid],
            [_].[owneridname] as [owneridname],
            [_].[statecodename] as [statecodename],
            [_].[statuscodename] as [statuscodename],
            [_].[activitytypecode] as [activitytypecode],
            [_].[activitytypecodename] as [activitytypecodename],
            [_].[Date] as [Date],
            convert(datetime2, [_].[Date Combined]) as [t0_0]
        from 
        (
            select [_].[actualend] as [actualend],
                [_].[subject] as [subject],
                [_].[description] as [description],
                [_].[statuscode] as [statuscode],
                [_].[scheduledend] as [scheduledend],
                [_].[actualstart] as [actualstart],
                [_].[statecode] as [statecode],
                [_].[activityid] as [activityid],
                [_].[owneridname] as [owneridname],
                [_].[statecodename] as [statecodename],
                [_].[statuscodename] as [statuscodename],
                [_].[activitytypecode] as [activitytypecode],
                [_].[activitytypecodename] as [activitytypecodename],
                case
                    when [_].[scheduledend] is null
                    then [_].[actualend]
                    else [_].[scheduledend]
                end as [Date],
                case
                    when [_].[actualend] is null
                    then [_].[scheduledend]
                    else [_].[actualend]
                end as [Date Combined]
            from 
            (
                select [actualend],
                    [subject],
                    [description],
                    [statuscode],
                    [scheduledend],
                    [actualstart],
                    [statecode],
                    [activityid],
                    [owneridname],
                    [statecodename],
                    [statuscodename],
                    [activitytypecode],
                    [activitytypecodename]
                from [dbo].[appointment] as [$Table]
            ) as [_]
        ) as [_]
        union all select [_].[actualend] as [actualend],
            [_].[subject] as [subject],
            [_].[description] as [description],
            [_].[statuscode] as [statuscode],
            [_].[scheduledend] as [scheduledend],
            [_].[actualstart] as [actualstart],
            [_].[statecode] as [statecode],
            [_].[activityid] as [activityid],
            [_].[owneridname] as [owneridname],
            [_].[statecodename] as [statecodename],
            [_].[statuscodename] as [statuscodename],
            [_].[activitytypecode] as [activitytypecode],
            [_].[activitytypecodename] as [activitytypecodename],
            [_].[Date] as [Date],
            convert(datetime2, [_].[Date Combined]) as [t0_0]
        from 
        (
            select [_].[subject] as [subject],
                [_].[actualend] as [actualend],
                [_].[activityid] as [activityid],
                [_].[scheduledend] as [scheduledend],
                [_].[actualstart] as [actualstart],
                [_].[description] as [description],
                [_].[statecode] as [statecode],
                [_].[statuscode] as [statuscode],
                [_].[statecodename] as [statecodename],
                [_].[owneridname] as [owneridname],
                [_].[statuscodename] as [statuscodename],
                [_].[activitytypecode] as [activitytypecode],
                [_].[activitytypecodename] as [activitytypecodename],
                case
                    when [_].[scheduledend] is null
                    then [_].[actualend]
                    else [_].[scheduledend]
                end as [Date],
                case
                    when [_].[actualend] is null
                    then [_].[scheduledend]
                    else [_].[actualend]
                end as [Date Combined]
            from 
            (
                select [subject],
                    [actualend],
                    [activityid],
                    [scheduledend],
                    [actualstart],
                    [description],
                    [statecode],
                    [statuscode],
                    [statecodename],
                    [owneridname],
                    [statuscodename],
                    [activitytypecode],
                    [activitytypecodename]
                from [dbo].[phonecall] as [$Table]
            ) as [_]
        ) as [_]
    )
    union all select [_].[actualend] as [actualend],
        [_].[subject] as [subject],
        [_].[description] as [description],
        [_].[statuscode] as [statuscode],
        [_].[scheduledend] as [scheduledend],
        [_].[actualstart] as [actualstart],
        [_].[statecode] as [statecode],
        [_].[activityid] as [activityid],
        [_].[owneridname] as [owneridname],
        [_].[statecodename] as [statecodename],
        [_].[statuscodename] as [statuscodename],
        [_].[activitytypecode] as [activitytypecode],
        [_].[activitytypecodename] as [activitytypecodename],
        [_].[Date] as [Date],
        convert(datetime2, [_].[Date Combined]) as [t0_0]
    from 
    (
        select [_].[subject] as [subject],
            [_].[actualend] as [actualend],
            [_].[actualstart] as [actualstart],
            [_].[statecode] as [statecode],
            [_].[activityid] as [activityid],
            [_].[scheduledend] as [scheduledend],
            [_].[description] as [description],
            [_].[statuscode] as [statuscode],
            [_].[owneridname] as [owneridname],
            [_].[statuscodename] as [statuscodename],
            [_].[statecodename] as [statecodename],
            [_].[activitytypecode] as [activitytypecode],
            [_].[activitytypecodename] as [activitytypecodename],
            case
                when [_].[scheduledend] is null
                then [_].[actualend]
                else [_].[scheduledend]
            end as [Date],
            case
                when [_].[actualend] is null
                then [_].[scheduledend]
                else [_].[actualend]
            end as [Date Combined]
        from 
        (
            select [subject],
                [actualend],
                [actualstart],
                [statecode],
                [activityid],
                [scheduledend],
                [description],
                [statuscode],
                [owneridname],
                [statuscodename],
                [statecodename],
                [activitytypecode],
                [activitytypecodename]
            from [dbo].[task] as [$Table]
        ) as [_]
    ) as [_]
) as [_]