Forum Discussion

jakubalbrecht's avatar
jakubalbrecht
Helper 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.

 

I have created a single data receiving and transforming the shared date from CRM via API. This shared dataset I use then in both reports using Direct Query.

 

The trouble is that in "KPIs report" I need to create new DAX calculated column which combines the original table received through Direct Query with new data received as a new data source directly to the "KPIs report". When I try to add the DAX calculated column, I got following error: "Column Person[Related individual targets count] cannot be pushed to the remote data source and cannot be used in this scenario."

 

Everything worked fine untill I have separated the CRM data into an individual dataset imported to the "KPIs report" using DirectQuery.

 

Can you please help me to find the solution how to combine data from DirectQuery dataset with other data to add DAX calculated column to the table imported via DirectQuery?

 

Thank you

 

Jakub Albrecht

 

------------

 

Details:

 

Data model details:

- using Direct query I load the "Persons table" from source dataset to report "KPIs report"

- in report "KPIs report" I add a DAX measure "Related individual targets count" which calculated how many individual targets/KPIs does specific person have assigned (source of the individual targets is the Excel sheed imported directly to the KPIs report dataset, not using DirectQuery of CRM data) - this seems to work fine

- in report "KPIs report" I then add a DAX calculated column "Person targets group" which determines the category of the each person in Persons table: in the Person has more then 0 individual targets, the column values is "Person with individual targets" otherwise the column values is "Others"

 

Person targets group =
IF(
    [Related individual targets count] > 0,
    "Person with individual targets",
    "Others"
)
 
Related individual targets count =
COUNTROWS(
    RELATEDTABLE(
        'Weekly targets - individual'
    )
)
Help

3 Replies

  • 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
      Helper 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
        Helper 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?