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

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