Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
I’m working with two datasets that don’t have a way of making a relation to each other. I need to subtract the number of crabapples from a specific store in the District dataset from the number of apples from the same specific store in the All Stores data set. If the number of apples is less than crabapples, then no subtraction is necessary.
I used UNION to join the two datasets, then used a big IF statement in a calculated column in the UNION table to figure out the correct subtraction. Then I went back to the original All Stores data set and used a RELATED function to get the new number of apples from the UNION table in a calculated column. And I got a circular dependency error.
Any ideas how I can do this subtraction?
Thanks so much!
All Stores Data Set
Store | Item | Units | New Units I'm looking for | Notes: |
Uptown | Apples | 7 | 7 | No crabapples are from the Uptown store in the District data set |
Uptown | Strawberries | 9 | 9 | |
Uptown | Grapes | 4 | 4 | |
Downtown | Apples | 5 | 2 | 3 crabapples are from the Downtown store in the District data set |
Downtown | Bananas | 3 | 3 | |
Downtown | Pears | 10 | 10 | |
Suburbs | Apples | 1 | 0 | 1 crabapple is from the Suburb store in the District data set |
Suburbs | Cantalopes | 6 | 6 | |
Suburbs | Pineapple | 2 | 2 | |
Rural | Apples | 4 | 4 | 10 crabapples are from the Rural store in the District data set. Because this are more than the 4 apples in the All Stores data set the New Units number stays at 4. |
Rural | Cantalopes | 1 | 1 | |
Rural | Pineapple | 2 | 2 |
District Data Set
Shop | Type | Amount |
Uptown | Bananas | 7 |
Uptown | Strawberries | 9 |
Uptown | Grapes | 4 |
Downtown | Bananas | 5 |
Downtown | Crabapples | 3 |
Downtown | Pears | 10 |
Suburbs | Crabapples | 1 |
Suburbs | Cantalopes | 6 |
Suburbs | Pineapple | 2 |
Rural | Crabapples | 10 |
Rural | Cantalopes | 1 |
Rural | Pineapple | 2 |
Solved! Go to Solution.
Hi @Anonymous
Try this for the calculated column 'New Units I'm looking for'
NewCol = IF ( Table1[Item] <> "Apple", Table1[Units], VAR Crabapples_ = LOOKUPVALUE ( Table2[Amount], Table2[Shop], Table1[Store], Table2[Type], Table1[Item] ) RETURN IF ( Crabapples_ > Table1[Units], Table1[Units], Table1[Units] - Crabapples_ ) )
Hi @Anonymous
Try this for the calculated column 'New Units I'm looking for'
NewCol = IF ( Table1[Item] <> "Apple", Table1[Units], VAR Crabapples_ = LOOKUPVALUE ( Table2[Amount], Table2[Shop], Table1[Store], Table2[Type], Table1[Item] ) RETURN IF ( Crabapples_ > Table1[Units], Table1[Units], Table1[Units] - Crabapples_ ) )
Hi AIB,
Thank you for this solution. It worked on the sample tables I had in my question. But I ran into problems when I tried to apply it to my real data.
I figured out that I needed more help with this question and made a separate post for it at Handling duplicates when performing a calculation on numbers from two unrelated tables
Thanks!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |