Write Dax query to create a agent buckets based on 2 different relationship

[Dilip7]

Hi Team ,

I want to write a Dax query to  create a agent buckets based on 2 different relationship for example 

Table A:

Key_Agent_MonthYear

Agent 

Sales 

Table B

Agent bucket 

Agent 

Key_Agent_MonthYear

Output:

• i need to create bucket based on 2 scenario for first 2 measures need to consider all agents till date like  if month is Jun 24 then bucket should be based on all cummulative agents .
• but for Active Fc we need to create bucket based on monthly agents like  if filter is Jun 24 then agent bucket should be based on Jun 24 agent records .

Agent Bucket	Sales	Total agent	Active agent
AB	for this Dax query should give records based on Table A[Agent] and Table B[Agent ]	for this Dax query should give records based on Table A[Agent] and Table B[Agent ]	for this Dax query should give records based on Table A[Key_Agent_MonthYear] and Table B[Key_Agent_MonthYear ]
SG
QC
SQC+

i tried 1 active and 1 non active relation but it is slow and if i use User relationship it gives error 

I tried Treatas and Intersect function as well but it is very slow .

I am new to PBI , pls let me know if any solution in Dax side or Power query side .

@rajendraongole1 

-Dilip

[FarhanJeelani · Accepted solution]

Hi @Dilip7 ,

The error indicates that you are encountering a problem with overlapping relationships in your data model, leading to ambiguity in Power BI. 

Check the Model relationship

Two Relationships Issue: It seems you have two paths (relationships) connecting your tables ( Table A and Table B ) to the same key columns. This causes Power BI to throw a tree-formation error.

Ensure you use only one active relationship between the tables while setting the second relationship as inactive. Use DAX to activate the inactive relationship dynamically where needed.

you need two types of DAX measures, one for cumulative agents (using the active relationship) and One for monthly agents (activating the inactive relationship dynamically).

To calculate the cumulative agent bucket based on all agents till the selected date:

CumulativeAgents =
CALCULATE(
    COUNTROWS('Table B'),
    FILTER(
        ALL('Table B'[Key_Agent_MonthYear]),
        'Table B'[Key_Agent_MonthYear] <= MAX('Table A'[Key_Agent_MonthYear])
    )
)

To calculate agent buckets for a specific month, use the USERRELATIONSHIP function to leverage the inactive relationship:

MonthlyAgents =
CALCULATE(
    COUNTROWS('Table B'),
    USERELATIONSHIP('Table A'[Key_Agent_MonthYear], 'Table B'[Key_Agent_MonthYear])
)

 

To group by agent bucket:

SalesByBucket =
SUMMARIZE(
    'Table B',
    'Table B'[Agent Bucket],
    "Total Sales", SUM('Table A'[Sales]),
    "Cumulative Agents", [CumulativeAgents],
    "Active Agents", [MonthlyAgents]
)

 

If you experience slowness:

Ensure your data model is optimized, with fewer calculated columns and a star schema structure.

Pre-aggregate or calculate buckets in Power Query if the dataset size is manageable.

Please mark this as solution if it helps you. Appreciate Kudos