Forum Discussion

jakubalbrecht's avatar
jakubalbrecht
Icon for Helper I rankHelper I
2 years ago

DAX calculated column extending Direct Query source table

Hello,   I have two Power BI reports: (1) KPIs report and (2) survey results. Each report has its own data sources but one data source is share for both reports: CRM data accessed through API.   ...
Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jakubalbrecht 

 

Consider using a Composite Model in Power BI. This feature allows you to combine DirectQuery and imported data within a single report. You can keep your CRM data connection as DirectQuery and import the Excel sheet data into the Power BI model. This way, you can create relationships between the tables and use DAX to create calculated columns or measures that span across both data sources.

 

 

 

 

 

Best Regards,

Jayleny

 

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

  • jakubalbrecht's avatar
    jakubalbrecht
    Icon for Helper I rankHelper I
    2 years ago

    Hi Anonymous ,

     

    thank you for your reply. I think I am already using Composite model in Power BI:

     

    But I am affraid there is a limitation in creating the DAX calculated column that for tables imported via DirectQuery it is not allowed to use data from different data source during adding the calculated columns:

     

    What I am trying to achieve is following:

    Level 2: Names coming from Persons table of the CRM database imported via DirectQuery

    Level 1: Categories of person (calculated column "Person targets group") calculated based on the information if the specific person in Persons table has been assigned with an individual target specified in Excel sheet (another data source outside CRM DirectQuery data source).

     

    Any idea how to solve the issue?

    • jakubalbrecht's avatar
      jakubalbrecht
      Icon for Helper I rankHelper I
      2 years ago

      I have also tried to create a new DAX calculated table on top of Persons table (coming from CRM database imported via DirectQuery) directly in the "KPIs report":

       

      PersonWithTargetsGroups =
      SUMMARIZECOLUMNS(
          'Persons'[Person ID],
          "Person targets group",
          IF(
              Persons[Related individual targets count] > 0,
              "Person with individual target",
              "Others"
          )
      )
       
      But when I try to create the new relation between Persons[Person ID] and PersonWithTargetGroups[Person ID], the error "A circular dependency was detected: ...":

       

      So creating new calculated table directly in the report's dataset on top of DirectQuery source dataset is neither the solution.

       

      Any other ideas?