The error you're encountering in your DAX measure likely stems from the inactive relationship between Person and Customer. Here's how you can rewrite the measure to address this issue:
1. Using VAR with LOOKUPVALUE:
The error you're encountering in your DAX measure likely stems from the inactive relationship between Person and Customer. Here's how you can rewrite the measure to address this issue:
1. Using VAR with LOOKUPVALUE:
Filled Map Measure =
VAR SelectedCostCenter = SELECTEDVALUE(CostCenter[Name])
VAR CustomerTable =
FILTER(
Customer,
RELATED(Person[CostCenterName]) = SelectedCostCenter
)
VAR Location =
LOOKUPVALUE(
Customer[Location],
Customer[CustomerID],
RELATED(Customer[CustomerID])
)
RETURN
IF(
ISBLANK(Location),
BLANK(),
Location
)Explanation:
- We define a variable SelectedCostCenter to capture the selected value from the CostCenter slicer.
- We filter the Customer table based on the CostCenter using RELATED(Person[CostCenterName]).
- We use LOOKUPVALUE to find the corresponding Location for the filtered customer based on their CustomerID.
- Finally, we return the Location if found, and BLANK otherwise.
2. Using CALCULATE with TREATAS:
The error you're encountering in your DAX measure likely stems from the inactive relationship between Person and Customer. Here's how you can rewrite the measure to address this issue:
1. Using VAR with LOOKUPVALUE:
Code snippet
Filled Map Measure =
VAR SelectedCostCenter = SELECTEDVALUE(CostCenter[Name])
VAR CustomerTable =
FILTER(
Customer,
RELATED(Person[CostCenterName]) = SelectedCostCenter
)
VAR Location =
LOOKUPVALUE(
Customer[Location],
Customer[CustomerID],
RELATED(Customer[CustomerID])
)
RETURN
IF(
ISBLANK(Location),
BLANK(),
Location
)Explanation:
- We define a variable SelectedCostCenter to capture the selected value from the CostCenter slicer.
- We filter the Customer table based on the CostCenter using RELATED(Person[CostCenterName]).
- We use LOOKUPVALUE to find the corresponding Location for the filtered customer based on their CustomerID.
- Finally, we return the Location if found, and BLANK otherwise.
2. Using CALCULATE with TREATAS:
Filled Map Measure =
VAR SelectedCostCenter = SELECTEDVALUE(CostCenter[Name])
VAR ActiveCustomerTable =
CALCULATE(
Customer,
TREATAS(
Person[CostCenterName],
SelectedCostCenter
)
)
VAR Location =
SUMX(
ActiveCustomerTable,
ActiveCustomerTable[Location]
)
RETURN
LocationExplanation:
- We define a variable SelectedCostCenter similar to the previous approach.
- We use CALCULATE with TREATAS to temporarily activate the relationship between Person and Customer for the selected cost center.
- We then use SUMX to iterate through the filtered ActiveCustomerTable and aggregate the Location values.
Both options achieve the same outcome: they filter the customer data based on the selected cost center and return the corresponding location for the map visualization. The choice between them might depend on your preference and coding style.
Remember:
- These solutions work with inactive relationships, but activating relationships is generally recommended for better performance and data consistency if possible.
- Ensure your data model is well-structured to minimize the need for workarounds like inactive relationships.
I hope this helps!
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
