Forum Discussion

Suhail-Arfaath's avatar
Suhail-Arfaath
New Member
2 years ago
Solved

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

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"
)

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.