How Treatas work with Physical relationship?

[Jack007]

I have a calendar look-up table that is physically connected to 2 datasets

The dataset below project list and PTP contains customer number in many to many relationship. So I decide to use treatas to create a virtual relationship. 

PT Revenue = CALCULATE(

    SUM('PTP% w o contingency'[revenue]

    ),

    TREATAS(

        VALUES(

            'project list'[customer number]),

            'PTP% w o contingency'[customer number]

))

When I create a visualization, PT Revenue appears correctly when only the customer number is displayed. However, when I try to drill down to fiscal year, PT Revenue does not appear. Can anyone help me understand this?

[lbendlin]

When you use TREATAS with table that are related you are messing with/overriding the data model joins. Sometimes it is benign (no impact) but other times you are overriding the "natural"  filters coming through the data model.  General recommendation: Don't do it.

[Jack007]

Is there a more efficient approach to achieve this task? I have a project list with customer numbers that recur, and I want to combine the revenue from a separate dataset that also has recurring customer numbers. The two datasets are connected through the customer number.

[lbendlin]

Are you sure the data model can't do that for you?

[Jack007]

I could try to link the datasets by customer number, but I learned that I should avoid many-to-many relationships since only a portion of the customer numbers between the datasets are the same.

[lbendlin]

If that is the case then you can consider normalizing your data so you get clean and covering dimension tables.