Error when splitting a measure

[AncaZ]

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!

[v-yangliu-msft]

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.

[AncaZ]

Hi @v-yangliu-msft !

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

[amitchandak]

@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

[AncaZ]

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.

[amitchandak]

@AncaZ ,
Can you share a sample pbix after removing sensitive data.

[AncaZ]

The report takes the data directly from the database, with DirectQuery. I'll try to see if the same issue comes up with import, and then I will be able to delete sensitive data and send you a sample.

Thank you for your support!