Alternate to calculate table (To enable data refresh when building composite model)

[Suhail-Arfaath]

I built a composite model (My Employees data is coming from Direct query "Unified Data Model" + local excel files)

Built the query such that to exculde few positions from employees table and create a seperate table. (Below is the code I am using, bascially it does the job)

But when using "CALCULATETABLE" Power BI Service is not allowing me to setup a refresh interval, because I am refering to a Direct query.
==========================================================================

==========================================================================

Question: Kindly help me to eliminte "CALCULATETABLE" from below code for table generation.

==========================================================================

Employees after excluding positions = CALCULATETABLE( SELECTCOLUMNS(

    Employees,

    Employees[Employee class],

    Employees[Employee status],

    Employees[Position ID],

    Employees[User ID],

    Employees[Tenure bucket],

    EXCEPT(

        VALUES (Employees[Position Title]),

        VALUES ('Exclude Positions list'[Position **bleep**le])

    ),

    EXCEPT(

        VALUES (Employees[User ID]),

        VALUES ('Exclude Employees list'[Employees_User ID])

    ),

    Employees[Tenure bucket] <> "less than 6 months"

)

==========================================================================

[v-tianyich-msft · Accepted solution]

Hi @Suhail-Arfaath ,

If the table uses data from DirectQuery, calculated tables aren’t refreshed. In the case with DirectQuery, the table will only reflect the changes after the dataset has been refreshed. If a table needs to use DirectQuery, it’s best to have the calculated table in DirectQuery as well.

And here's an alternative way to achieve the same result without using CALCULATETABLE. You can use the FILTER function instead. Here's how you can modify your code:

Employees after excluding positions =
FILTER (
    SELECTCOLUMNS (
        Employees,
        Employees[Employee class],
        Employees[Employee status],
        Employees[Position ID],
        Employees[User ID],
        Employees[Tenure bucket]
    ),
    NOT (
        Employees[Position Title]
            IN VALUES ( 'Exclude Positions list'[Position **bleep**le] )
    )
        && NOT (
            Employees[User ID] IN VALUES ( 'Exclude Employees list'[Employees_User ID] )
        )
            && Employees[Tenure bucket] <> "less than 6 months"
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.