Forum Discussion
Error when splitting a measure
Hi!
I am trying to build a report based on a DirectQuery data source. I calculated some indicators such as the one below:
Number_of_orders = CALCULATE(DISTINCTCOUNT(orders[id]), FILTER('orders', 'orders'[value] > 0)), where 'orders'[value] is decimal with both positive and negative values.
It works fine by itself, I can use a slicer to filter it, but if I try to split it in the same visual, there is an error. For example, I try to make a table with this calculated measure as Value and the region on column (text variable) or a chart by hours, with the hour on the axis (and that's an integer). I get this error in both cases.
The error is: OLE DB or ODBC error: Unable to cast object of type 'System.Decimal' to type 'System.Char[]'
If I take out the FILTER('orders', 'orders'[value] > 0) condition and only leave the DISTINCTCOUNT, it works fine. It also works with other filters, e.g. if I filter only by date.
The strange thing is that this kind of formula worked before and I don't understand why it's not working now.
Any ideas about what could cause this error?
Thank you for your help!
6 Replies
AncaZ , Are you trying to create a calculated table or visual table. A new calculated table will not take any slicer value. for this System.Decimal' to type 'System.Char[]'
please share screenshot
Hi, amitchandak ! I am trying to create a visual table. In fact, it's a matrix. If the matrix has nothing on columns or rows, the values come out ok. As soon as I place something on columns or rows, I get this error. And it's the same with charts: if there is something on the axis, I get this error.
AncaZ ,
Can you share a sample pbix after removing sensitive data.
Hi AncaZ ,
What do you mean by "split", can you explain it with an analogy?
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous !
What I want to create is a matrix visual that has for example Regions on columns, and number of orders as value. Basically, the number of orders gets "split" by regions. Maybe it wasn't the best choice of words :).
The error that I mentioned appears when I use a calculated measure for which I filter the data in some way.
E.g.:
- this one gets an error when I use it in a matrix with something (like Regions) on columns:
Number_of_orders = CALCULATE(DISTINCTCOUNT(orders[id]), FILTER('orders', 'orders'[value] > 0))
- this one doesn't get an error:
Number_of_orders = DISTINCTCOUNT(orders[id])
Thank you!
Anca
