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