Pulling values off an inactive relationship

[Gwhiz]

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?

[v-juanli-msft]

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]

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]

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

[Gwhiz]

Here are the other two tables