Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Using Inactive Relationship to Fill Gaps in Data

Hello Everyone,

 

I am trying to use an inactive relationship to fill gaps in my data. I have a fact table and a dimension table. There are two relationships, 1 active and 1 inactive, pictured below:

 

 

In my data table (Data), there are 2 columns: Contract # and Contract Collective #. My Dimension table (Contract Header) is joined to the Contract # so I can pull in the customer and vendor information associated with the Contract #. The combined data looks like the left table below (right table is my dimension table):

 

 

Note how on the bottom row, the Contract # and Contract Collective # don't match. What I want to do is that when the Contract # and Contract Collective # don't match and a null is returned for the vendor, I want to use the inactive relationship (which is based on Contract Collective #) to return the vendor for the Contract Collective # (which would be Samm's Club in this case). Can anyone help with this? 

 

 

 

 

Need Help

11 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous add a new column like this as an example for a vendor. 

     

    In my example, the Customer represents your Contract Header table and the Table represents your data table.

     

    Vendor 1 = 
VAR __vendor = RELATED ( Customer[Vendor] )
VAR __vendorIfBlank = 
IF ( COALESCE( __vendor, "" ) = "", 
    MAXX ( 
        Customer, 
        CALCULATE ( 
            MAX ( Customer[Vendor] ), 
            USERELATIONSHIP ( Customer[Column1], 'Table'[Col2]  ) 
        ) 
    ),
    __vendor 
)
RETURN __vendorIfBlank

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I tried it, and I was unable to use COALESCE so I just used ISBLANK instead. This is what happened when I tried it: 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I changed my data table titles to match your use of them 

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      It's all good, I found a solution!

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous good to hear, I would recommend pasting the solution so that others I can get help from it.s