Forum Discussion

Gwhiz's avatar
Gwhiz
Icon for Helper I rankHelper I
7 years ago

Pulling values off an inactive relationship

I am trying to pull the value of a parent account number which has an inactive relationship to another table that contains all account numbers and a qty column.

 

The child account number has the active relationship to other tables but I can't find a formula to pull the value for the parent account number only.

 

 

 

The only formulas I can come up with are the following:

 

 

 
parent qty = 
calculate( 
SUM(addontable[qty]),
USERELATIONSHIP(Parrentaccounts[parrentaccountnumber],addontable[corpaccountnumber]))

 And it just returns the Qty of the child accounts, How can I pull the Qty of the parent account only?

 

 

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Gwhiz 

    I suggest you transform your data structure as below

    Open Edit queries, in "Parentaccounts", select two columns and then select "unpivot columns"

    Then select "close&&apply", return to Data view, create relatinship as below.

    Create a measure in "Parentaccounts",

    Measure = SUM(addontable[qty])

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Gwhiz's avatar
      Gwhiz
      Icon for Helper I rankHelper I
      7 years ago

      Thanks for replying v-juanli-msft however your method doesn't solve the main problem of getting the parent accounts Qty value to show only when the child account number is chosen.

       

      The reason for this is to quickly compare the Qty from the Parent account to the child account in a big dataset. And each day 10-30 child accounts will be filtered for and having this formula will automate that comparison.

       

      Is this even possible with DAX?

      • Gwhiz's avatar
        Gwhiz
        Icon for Helper I rankHelper I
        7 years ago

        I think the only solution to this is to perform a join on the two tables