Forum Discussion

isjoycewang's avatar
isjoycewang
Icon for Solution Supplier rankSolution Supplier
2 years ago
Solved

Combined slicer for direct query and import data source

Hi Experts,

 

We're trying to combine one pre-defined dataset from direct query to other tables from import, details please see below screenshot.

 

The goal is to have summary result and apply slicers simutaneusly on both data source. 

We've tried to use SUMMARIZECOLUMNS() to create summary table but the calculated table didn't work in Power BI service. Since we'll have around 20 multi-select slicers in actual, writing one measure considering all filter scenarios seems too complex and hard to maintain.

 

Attached the demo file with simplified data while without direct query connection due to sensitive data. Any thoughts would be greatly appreciated. Thanks!

 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    isjoycewang,

     

    Are you able to access the source of table Customer? If so, import that table into the composite model and then the calculated table should work. For example, if the DirectQuery model uses dataflow Customer, then the composite model should import the dataflow Customer. Once the Customer and Parent tables are imported to the composite model, you can create CustomerMaster in either Power Query or DAX.

3 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    isjoycewang,

     

    Create a master dimension table like the one below (DAX calculated table):

     

    CustomerMaster =
DISTINCT (
    UNION (
        DISTINCT ( Customer[Customer Name] ),
        DISTINCT ( Parent[CustomerName] )
    )
)

     

    Then create a 1:* unidirectional relationship between CustomerMaster and Customer, and CustomerMaster and Parent. CustomerMaster will allow you to filter each group of tables in your model.

    • isjoycewang's avatar
      isjoycewang
      Icon for Solution Supplier rankSolution Supplier
      2 years ago

      Hi DataInsights,

       

      Thanks for the advice, but we've tried and found that the calculated table reference from Direct Query is not supported in the Power BI Service.

      Is there any possible workaround to avoid creating another new calculated table? Thanks! 

       

       

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        2 years ago

        isjoycewang,

         

        Are you able to access the source of table Customer? If so, import that table into the composite model and then the calculated table should work. For example, if the DirectQuery model uses dataflow Customer, then the composite model should import the dataflow Customer. Once the Customer and Parent tables are imported to the composite model, you can create CustomerMaster in either Power Query or DAX.