Error MdxScript(Model) (196,47)

[Mostafa-Hussien]

Hi

I am trying to refresh a pbix file in report server that always used to successfully refresh. This time it refuses to refresh giving this error

I have tried looking for any measure or calculated column that has 196 lines and deleted them all. The same error appears again

What is that and how to solve it ?

[v-jianpeng-msft]

Thank you hackcrr

Hi, @Mostafa-Hussien 

I see that your error is similar to the one shown by Super user.
Pls have you solved the current problem? If so, you can share your solution here and mark it as a solution and mark the helpful reply as a solution, so that others in the community can quickly find solutions when they encounter similar problems, saving them time.
Your cooperation will work with us to promote the development of the community. Thank you again. 

Best Regards

Jianpeng Li

[hackcrr]

@Mostafa-Hussien 

The error specifies MdxScript(Model) (196, 47), which means the error occurred around character 47 of line 196 of the model script. However, because the line number in the error message may not always correspond directly to your DAX expression, you need to systematically check your DAX expressions, especially those that may return a table instead of a single value.
Here are some DAX functions you can check based on your situation:
1. If you are using LOOKUPVALUE, make sure it returns only a single value. If there are situations where multiple rows can be matched, you will receive this error.
2. When using RELATED, make sure the relationship is one-to-one or your context does not result in multiple related rows.
3. This function should return a single value from the column, but if there are multiple values, it will return blank or raise an error. Please use SELECTEDVALUE with caution.
4. First/Last Functions FIRSTNONBLANK: Functions such as FIRSTNONBLANK or LASTNONBLANK are sometimes used to get a single value from a table. Make sure they are used correctly and that the table does not return multiple valid rows.
5. Ensure that any table expressions are correctly aggregated to a single value when expected (for example, using MAX, MIN, SUM, etc.).

If you use VALUES or DISTINCT, these functions return a table of unique values. If you need a single value, use MAX, MIN, FIRST, or another scalar-returning function instead.

Check for recently modified or complex calculated columns and measures. Pay special attention to those that may involve tables or relationships.

You have access to DAX Studio, so you can run parts of your DAX code to see if they run as expected and narrow down the problem.

hackcrr

If I have answered your question, please mark my reply as solution and kudos to this post, thank you!

[Mostafa-Hussien]

Consequently I am trying to comment all the measures to see when the file refreshes and identify the faulty one. This is a tediuos and time-consuming process, since I have lots of measures using the functions you mentioned, but seems the only way right now

Does this error measures describe a faulty DAX only ? Or can that error message correspond to other areas like M code or model relationships ?

[hackcrr · Accepted solution]

@Mostafa-Hussien 

You should check your computed columns, the current error occurs when multiple values ​​are passed to the computed column. Make sure that each expression used in the computed column returns a unique value.

If you refresh the data source and multiple values ​​are returned in the new data according to your previous calculation logic, the current error will also occur.

hackcrr

If I have answered your question, please mark my reply as solution and kudos to this post, thank you!