Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dealing with duplicate lines when performing a calculation on numbers from two unrelated tables

Hi All!

I posted a question several months ago about performing a calculation on numbers from two unrelated tables. AIB helped me with a solution to my original question. (Thanks heaps, AIB!) But it turns out that I didn’t understand my data well enough to ask my question correctly. Now I’m back to try again.

 

My original question was:

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

 

Which was solved with AIB’s solution:

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_ )
)

 

I applied the solution to my actual data and wrestled with a “table of multiple values was supplied where a single value was expected” error for far too long. It turns out that I have lines with duplicate shop and type entries in one of my data tables. Of course, I cannot change how the data is given to me nor simply delete the duplicate lines.

 

I tried the solution using a column with a unique entry for each line that combines the shop and type entries for the line plus a number indicating if that line was a duplicate line and a column that summed all crabapple amounts for a specific shop. The “table of multiple values was supplied where a single value was expected” error went away but I can’t get the variable Crabapples_ to return anything but a blank.

 

Can anybody show me where I’m going wrong with this? 

 

This is the Apples and Crabapples pbix file that shows the problem I’m having. The problem calculated column is titled "Trying to Create New Units I'm Looking for column" in the "All Stores" table. The "What VAR Crabapples_ returns" column shows that the Crabapples variable returns a blank for every line. The "District" table is the table with duplicate lines where I used the "Index" and "Incidence number for Shop + Type" columns to make the "Shop + Type + Incidence" column with unique entries for each line in the table. 

 

Thanks so much for your help!!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I changed two calculated columns as follows.

 

What VAR Crabapples_ returns = VAR Crabapples_ = 
LOOKUPVALUE (
District[Adding multiple Crabapple entries],
District[Shop],'All Stores'[Store],
District[Type], "Crabapples")
RETURN
Crabapples_

Trying to create New Units I'm looking for column = 
IF (
'All Stores'[Item] <> "Apples",
'All Stores'[Units],
VAR Crabapples_ = 
LOOKUPVALUE (
District[Adding multiple Crabapple entries],
District[Shop],'All Stores'[Store],
District[Type], "Crabapples"
)
RETURN
IF ( Crabapples_ > 'All Stores'[Units], 'All Stores'[Units], 'All Stores'[Units] - Crabapples_)
)

 

 

Result:

d1.png

 

 

If I misunderstand your description, please show me your expected result. Do mask sensitive data before up loading. I am glad to solve the problem for you. Thanks.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi V-Alq-Msft and Rajulshah,

 

Thanks for your replies and help!

 

Specifically, Rajulshah pointed out that my logic in looking for a match with crabapples in both tables was wrong as crabapples doesn’t appear in one table. Since there aren’t crabapples in one table, the LOOKUPVALUE function doesn’t return any value because there isn’t a match found.

 

V-Alq-Msft’s solution where the LOOKUPVALUE function doesn’t try to match the fruit names in each table. I didn’t have to make the duplicate crabapples entries unique to make the LOOKUPVALUE function work because the “Adding multiple Crabapple entries” column has the same entry for each line with matching Store and Shop names and the fruit Type “Crabapples”. This means that the LOOKUPVALUE function doesn’t encounter multiple values for the same Store, just the same value multiple times.

 

Again, thanks for your help! I’m sure I’ll be asking for more soon!

v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I changed two calculated columns as follows.

 

What VAR Crabapples_ returns = VAR Crabapples_ = 
LOOKUPVALUE (
District[Adding multiple Crabapple entries],
District[Shop],'All Stores'[Store],
District[Type], "Crabapples")
RETURN
Crabapples_

Trying to create New Units I'm looking for column = 
IF (
'All Stores'[Item] <> "Apples",
'All Stores'[Units],
VAR Crabapples_ = 
LOOKUPVALUE (
District[Adding multiple Crabapple entries],
District[Shop],'All Stores'[Store],
District[Type], "Crabapples"
)
RETURN
IF ( Crabapples_ > 'All Stores'[Units], 'All Stores'[Units], 'All Stores'[Units] - Crabapples_)
)

 

 

Result:

d1.png

 

 

If I misunderstand your description, please show me your expected result. Do mask sensitive data before up loading. I am glad to solve the problem for you. Thanks.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rajulshah
Super User
Super User

Hello @Anonymous,

Please use the following DAX:

What VAR Crabapples_ returns = 
VAR Crabapples_ = 
LOOKUPVALUE (
District[Adding multiple Crabapple entries],
District[Shop + Type + Incidence],'All Stores'[Store + Item])
RETURN
Crabapples_

I didn't understand why did you use "Crabapples" condition in LOOKUPVALUE function. It returned blank because there was no value as 'Crabapples' in Store table. Please let me know if you want otherwise.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.