Creating calculated table that can have relationship

[fjellfisk]

I need som help creating a dax expession for a calculated table

Scenario (simplified)
Dim Tables:
Employee (EmployeeID)
Date (DateID)
Fact:

Sales (EmployeeID, DateID, SalesAmt)

Measure:
Sales amount 

I want to create a calculated table that contains

EmployeeID, DateID, Sales Amount

With this new calculated table, I want to add relationships to the dim tables. When I try this, I get circular reference errors. I want to kind of disconnect the calculated table and then connect it again. My example is very simplified. The issue I have is that the measure is slow because of detailed calculation logic. So I want to go for longer processing time for the dataset and better perfomance for endusers.

[fjellfisk]

The challenge for me is the measure for sales amount. the measure is like this:

Sales Amount=CALCULATE(SUM('Sales'[Amount]),FILTER(ALL('Date'), 'Date'[DateID<=MAX('Date'[DateID]) && 'Date'[DateID]>=MIN('Date'[DateID))

When using this Measure in the calculated table, I get circular reference when creating relationship between date and Sales table. My hope was to "disconnect" the measure so I can create this relationship.

[v-cgao-msft]

Hi @fjellfisk ,

Not sure. Please try this measure:

Sales Amount =
CALCULATE (
    SUM ( 'Sales'[Amount] ),
    FILTER (
        'Date',
        'Date'[DateID] <= MAX ( 'Date'[DateID] )
            && 'Date'[DateID] >= MIN ( 'Date'[DateID] )
    ),
    ALLEXCEPT ( 'Date', 'Date'[DateID] )
)

If this doesn't work, can you provide a sanitized file for testing?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[fjellfisk]

Thanks, will try this. my problem was that I got circular reference when adding relations. I thought I tried to run summarize on sales tbl, but I'll try again! 🙂 

Thank you! 😊

[v-cgao-msft]

Hi @fjellfisk ,

Any update?

Best Regards,
Gao
Community Support Team

[v-cgao-msft]

Hi @fjellfisk ,

I created a sample file based on the description.

Sales:

Employee:

Employee = DISTINCT('Sales'[EmployeeID])

Date:

Date = CALENDAR(MIN('Sales'[DateID]),MAX('Sales'[DateID]))

Measure:

Sales amount = SUM('Sales'[SalesAmt])

Table:

Table = 
SUMMARIZE('Sales','Sales'[EmployeeID],'Sales'[DateID],"Sales Amount",[Sales amount])

relationships:

I am not sure if these cover your data, if not, please provide more details about the question and feel free to contact us.

If possible, you could also consider referencing the sales table in the PowerQuery Eidtor and using m-code to perform the calculations, but it's not clear what the logic is for this part of the measure. The advantage of this is that it only takes up resources when the data is refreshed and there are no circular reference errors

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data