DAX to calculate sum based on another table

[manojsv]

Hello All,

I have created a one to many relationship between Dim_ProductCost which has the source as excel  and Fact_ProductsList table uses direct query from SQLServer.  I wanted to calculate the sum of cost of all products in the fact table by looking up the corresponding cost value from the dim table using productcostid field.   The count i am expecting to get is 199.25 as shown in the table and have used the following measure to get the count

_m_sum_costsaving = CALCULATE(SUMX('Fact_ProductsList', RELATED(Dim_ProductCost(ProductCostValue))

I am getting the following error when using the excel(Dim_ProductCost) stored locally and other table (Fact_ProductsList) uses direct query. but, not getting any error if i imported the fact table. Can you help us in getting resolved this issue when using both the direct query and lookup data from the local file.   I would appreciate any suggestions to acheive the desired result.

"The column 'Dim_ProductCost(ProductCostValue' either doesn't exist or doesn't have a relationship to any table available in the current context."

  Excel Data(Dim_ProductCost) has the cost info like this

ProductCostID	ProductCostValue
1	12
2	24
3	28.65
4	18.9
5	50.6
6	18.1

Table 2(Fact_ProductsList table) has the product related info.

ProductID	ProductName	ProductCostID	LookupValues from ProductCost table
12	A	1	12
14	B	5	50.6
19	C	3	28.65
20	D	1	12
21	E	2	24
12	A	1	12
32	F	2	24
15	G	1	12
32	H	2	24
			199.25

[Icey]

Hi @manojsv ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[Icey · Accepted solution]

Hi @manojsv ,

For your issue, it is caused by the Mixed mode in which Related doesn’t work well. You can create a measure like so:

Measure =
VAR t =
    FILTER (
        CROSSJOIN (
            Fact_ProductsList,
            SELECTCOLUMNS (
                Dim_ProductCost,
                "Product_Cost_Value", Dim_ProductCost[ProductCostValue],
                "Product_Cost_ID", Dim_ProductCost[ProductCostID]
            )
        ),
        [Product_Cost_ID] = Fact_ProductsList[ProductCostID]
    )
RETURN
    SUMX ( t, [Product_Cost_Value] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.