cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

Error - multiple values was supplied where a single value

Hello,

 

I have this code which was working fine but when I refreshed the data it started to show this error:

"A table of multiple values was supplied where a single value was expected."

 

OTIF Summary = 
   CALCULATETABLE(
        SUMMARIZE( 
    'OTIF Main'
    , 'OTIF Main'[PO Number]
    , 'OTIF Main'[PO Date]
    , 'OTIF Main'[Requested Qty UoM]
    , "Vendor", CALCULATE(DISTINCT('OTIF Main'[Vendor Number]))
    , "Created by", CALCULATE(DISTINCT('OTIF Main'[Created by]))
    , "Plant", CALCULATE(DISTINCT('OTIF Main'[Plant])) 
    )
   , FILTER('OTIF Main','OTIF Main'[Transaction Validity] = "Keep")
   , FILTER('OTIF Main', DATEDIFF(TODAY(), 'OTIF Main'[Requested Delivery Date], DAY) < -13 )
   )

 

Can't find why and which line is throwing error and how to correct it.  

 

You help is much appreciated.

Thanks

1 ACCEPTED SOLUTION

As you cannot use MAX or MIN function with text to force unicity for that columns, you can use FIRSTNONBLANK or LASTNONBLANK functions, only if the vendor name is not somenthing crucial in your report. Is a workaround we often use in Sql queries, when details are not important. 

You can check this post from community for reference https://community.powerbi.com/t5/Community-Blog/LASTNONBLANK-amp-FIRSTNONBLANK-How-To-Use-This-Two-D....

 

Is important to know that the values for each column will be first or last (depends on the function used) sorted in alphabetical order,

 

View solution in original post

8 REPLIES 8
MiraAciu
Regular Visitor

Hi, 

It seems that, at refresh, one of Vendor, Created by or Plant has at least 2 distinct

values for the same combination of the values for the rest of the columns. Start to investigate there. 

After that decide what you want to keep for those 3 columns and ask here for help if you need further assistance.

@amitchandak 

Distinct is just returning the text - vendor, plant and creator names.  I don't want those to be the criteria of table summary. 

 

@MiraAciu can i just get first vendor, plant and creator name to complete the table.  I don't want these three fields to be the table summary criteria.

As you cannot use MAX or MIN function with text to force unicity for that columns, you can use FIRSTNONBLANK or LASTNONBLANK functions, only if the vendor name is not somenthing crucial in your report. Is a workaround we often use in Sql queries, when details are not important. 

You can check this post from community for reference https://community.powerbi.com/t5/Community-Blog/LASTNONBLANK-amp-FIRSTNONBLANK-How-To-Use-This-Two-D....

 

Is important to know that the values for each column will be first or last (depends on the function used) sorted in alphabetical order,

 

View solution in original post

@MiraAciu  Yes. FIRSTNONBLANK worked.  Thanks

 

@amitchandak  Thanks for looking into this.

@mb0307 , then it should be min/max or should be grouped? No need add like a measure.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User
Super User

@mb0307 , I am assuming you are creating a table. Not measure. Seems fine.

Try like

 

OTIF Summary =
SUMMARIZE( filter(
'OTIF Main' ,
'OTIF Main','OTIF Main'[Transaction Validity] = "Keep" && DATEDIFF(TODAY(), 'OTIF Main'[Requested Delivery Date], DAY) < -13 )
, 'OTIF Main'[PO Number]
, 'OTIF Main'[PO Date]
, 'OTIF Main'[Requested Qty UoM]
, "Vendor", CALCULATE(DISTINCT('OTIF Main'[Vendor Number]))
, "Created by", CALCULATE(DISTINCT('OTIF Main'[Created by]))
, "Plant", CALCULATE(DISTINCT('OTIF Main'[Plant]))
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak  Thanks. Yes I am creating a table but this is still showing the same error.  Please see below.  

 

Screenshot 2021-01-19 131445.jpg

@mb0307 , I think distinct is the issue. It should be countdistinct

 

OTIF Summary =
CALCULATETABLE(
SUMMARIZE(
'OTIF Main'
, 'OTIF Main'[PO Number]
, 'OTIF Main'[PO Date]
, 'OTIF Main'[Requested Qty UoM]
, "Vendor", CALCULATE(COUNTDISTINCT('OTIF Main'[Vendor Number]))
, "Created by", CALCULATE(COUNTDISTINCT('OTIF Main'[Created by]))
, "Plant", CALCULATE(COUNTDISTINCT('OTIF Main'[Plant]))
)
, FILTER('OTIF Main','OTIF Main'[Transaction Validity] = "Keep")
, FILTER('OTIF Main', DATEDIFF(TODAY(), 'OTIF Main'[Requested Delivery Date], DAY) < -13 )
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!