Forum Discussion

anusha_2023's avatar
anusha_2023
Icon for Helper IV rankHelper IV
2 years ago

Filled Map with inactive relationship

I am trying to visualize country column data from the Customer dimension using the filled map

 

 

I have a slicer from CostCenter[Name] and need to reflect country data in the map. Due to the inactive relationship between Person and Customer, I am using Userelationship and writing the following measure to include the Location field for the Map instead Country column directly from the Customer dimension.

But I am getting the following error. Please help me to rewrite the measure. The above relationships I could not change to active since I have other facts and dimensions that might lead to circular relations so I need to work with measure to be inactive only.

 

 

Need Help

8 Replies

    • anusha_2023's avatar
      anusha_2023
      Icon for Helper IV rankHelper IV
      2 years ago

      Thank you very much for your reply. Now I got only one city instead CostCenter has many customer countries how an I get all coutries when filtered from CostCenter and get Customer Countries to give as input to the Location in the filled map.

      Measure Customer Country = CALCULATE(

               MAX ( Customer[Country] ),USERELATIONSHIP

      (Person[Id],Customer[Id]))

      Right now even though I tried to give this as input the filled map Location it is not acceptable by the visual.

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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
    Location

    Explanation:

    • 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.