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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wgm13
Frequent Visitor

Issues with refreshing dataset in the webservice of PowerBI

Hi, 

i am having issues with the refresing proccess of a dateset. When i go to powerbi.com, i find a warning icon next to the a dataset, but when i click on that icon, the message presented is the following:

 

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

 

This type of error is common when there is an incorrect use of the function LOOKUPVALUE, i try to identify the incorrect use of this function, but everything is in perfect conditions, and there are not warning icons in measures or  columns in this report; the other is the use of VALUES, but i am not using this function in my powerbi report.

 

Can anybody help me with this issue?

1 ACCEPTED SOLUTION
wgm13
Frequent Visitor

Hi @aj1973 ,

 

First of all, really appreciate the attention you had on my issue. I already soved it, the issue was that i had a table that initially had just 12 month (this table appeared in the model on January last year), and the function was the following: "Summarize(New_table_in_the_model,New_table_in_the_model[Country],"AVG_CONSUMPTION",Calculate(Average(New_table_in_the_model[VOLUME]),New_table_in_the_model[TYPE]="BUDGET",DATESBETWEEN(New_table_in_the_model[DATE],-11,MONTH),LASTDATE(New_table_in_the_model[DATE]))", as at the beginning of the new table there just were 12 months, there was not any problem, but specifically on wednesday last week, one of the powerapps user, gathered new data with the app, adding 12 new months to the table, and causing the duplication of months in the model, in consecuense, i had to change the function to the following: "Summarize(New_table_in_the_model,New_table_in_the_model[Country],"AVG_CONSUMPTION",Calculate(Average(New_table_in_the_model[VOLUME]),New_table_in_the_model[TYPE]="BUDGET",DATESBETWEEN(LASTDATE(New_table_in_the_model[DATE]),-11,MONTH),LASTDATE(New_table_in_the_model[DATE]))", and now the power bi report is working perfectly as before.

 

Regards,

View solution in original post

14 REPLIES 14
wgm13
Frequent Visitor

Hi @aj1973, could You please number a couple of causes for that inconvenient according to your experience?

 

Regards

aj1973
Community Champion
Community Champion

Hi @wgm13 

A measure in a perfect condition doesn't mean it is acceptable and can't be replaced for better performance.

There are several ways to avoid using LOOKUPVALUE in PowerBi desktop but that will depend on your Model.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi @aj1973 ,

Understood, but my inconvinient is that i cannot have my powerbi report working as expected, and when i open it in the desktop mode, there is not any warning icon or any indicator that indicate an error, every measure and added columns are working properly, and when i refresh the report in the desktop mode, at the end of this process, shows me the error previously presented, and in th web mode apears the following notification:

 

wgm13_0-1644620995846.png

Frankly, every time this error apeared, it was because of a LOOKUPVALUE function that is finding two matches in the same table or the VALUES function used in an scalar pattern.

aj1973
Community Champion
Community Champion

Well if you are sure that the LOOKUPVALUE is the issue then use different functions or Modeling....sorry I can't help further if I don't see the PBIX.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi @aj1973, I am not saying that my issue is a LOOKUPVALUE, if it would have been the issue, i would have solved it by using a different function as you say in the previous comment, what i mean is that the issue i am presenting shows me the same notification than LOOKUPVALUE or VALUES misusing. could you please number a couple of causes for that inconvenient according to your experience?

 

Really appreciate your collaboration.

 

Regars.

 

 

aj1973
Community Champion
Community Champion

@wgm13 

Maybe an ambiguise relationship between tables is in place, or circular dependency, many to many relationship, bidirectional...Could screenshot your Model?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi, of course:

 

wgm13_0-1644677726758.png

Regards,

 

aj1973
Community Champion
Community Champion

From a glance I can tell that you have many Bidirectional relationships why?

That creates ambiguities every where in your model and it is bad. That's the problem 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi @aj1973,

I changed all the links to unidirectional, and still presenting same error notification when refresing the dataset, then tried removing every link (No links in the model), and incredibly, still showing the same error notification. Really don' t have any idea of what is happening in my model, and in fact, the power bi report was working correctly until last monday approximately, and basically it stopped working, that last event is courious. Could you recommend anything else?... Bellow the image with the no link model:

 

wgm13_0-1644751691403.png

 

Regards

aj1973
Community Champion
Community Champion

@wgm13 

The report was still working and refreshing correctly until last Monday? So what has changed after that monday? Was there a change at the source? what is the source? Did you add a calculated table to your model? Did you add a measure to your Model?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi @aj1973 

The report was still working and refreshing correctly until last Monday? Yes

 Was there a change at the source? what is the source? Did you add a calculated table to your model? Did you add a measure to your Model? Nothing, the last change i did was on november 2021, and since that date, the powerBi report never presented inconvenients until the last monday... What i have not told you is that the data in the majority of those data sources comes from a powerapps app. Can be an incorrect registry in the data sources that maybe is cousing this issue? 

Did you add a calculated table to your model? Did you add a measure to your Model?Nothing, the last change i did was on november 2021, and since that date, the powerBi report never presented inconvenients.

 

Regards

 

aj1973
Community Champion
Community Champion

Well yes that where could be the change had happened then. Check it out.

What App did you create Canvas, Model Driven or Portal?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

wgm13
Frequent Visitor

Hi @aj1973 ,

 

First of all, really appreciate the attention you had on my issue. I already soved it, the issue was that i had a table that initially had just 12 month (this table appeared in the model on January last year), and the function was the following: "Summarize(New_table_in_the_model,New_table_in_the_model[Country],"AVG_CONSUMPTION",Calculate(Average(New_table_in_the_model[VOLUME]),New_table_in_the_model[TYPE]="BUDGET",DATESBETWEEN(New_table_in_the_model[DATE],-11,MONTH),LASTDATE(New_table_in_the_model[DATE]))", as at the beginning of the new table there just were 12 months, there was not any problem, but specifically on wednesday last week, one of the powerapps user, gathered new data with the app, adding 12 new months to the table, and causing the duplication of months in the model, in consecuense, i had to change the function to the following: "Summarize(New_table_in_the_model,New_table_in_the_model[Country],"AVG_CONSUMPTION",Calculate(Average(New_table_in_the_model[VOLUME]),New_table_in_the_model[TYPE]="BUDGET",DATESBETWEEN(LASTDATE(New_table_in_the_model[DATE]),-11,MONTH),LASTDATE(New_table_in_the_model[DATE]))", and now the power bi report is working perfectly as before.

 

Regards,

aj1973
Community Champion
Community Champion

Glad I helped you out through this endeavour 🙂

Please don't forget to accept this thread as solution for the rest of the community 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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