Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 |
Solved! Go to 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.
Hi @manojsv ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
107 | |
88 | |
75 | |
66 |
User | Count |
---|---|
125 | |
114 | |
98 | |
81 | |
73 |