Forum Discussion

Frequent Visitor

9 years ago

Solved

Power Bi Service Measures

I am creating a report in Power BI desktop and using various measures. All measures work appropriately on desktop. An example of one of the measures is shown below. It is a direct query to SQL server...

dti
9 years ago
I was formatting a column in Direct Query as a decimal number. When I used this column in a slicer, it would cause my measures to stop working in the Power BI Service. Once I changed the column to type 'any', the measures worked. So yes, the problem was solved by changing the column type to 'any' from decimal number.

Community Champion

9 years ago

What version of SQL Server?

Frequent Visitor

9 years ago

2014 Express

dti
Frequent Visitor
9 years ago
I have narrowed it down further - I am using a slicer in my report. When I remove that slicer, all the measures work with no problems. Using direct query can you not use a slicer in parallell with measures?
- dti
  Frequent Visitor
  9 years ago
  Another update:
  
  I have discovered that any time I filter, whether that is using a slicer or by any other visualization, the error is created in the Power BI service. Everything works fine in the Power BI desktop. I am wondering if it has something to do with the gateway and its interaction with SQL Server 2014 Express.
  - v-haibl-msft
    Microsoft Employee
    9 years ago
    dti
    
    I just test with latest PBI Desktop and get data from SQL Server 2016 in DirectQuery mode. The measure works well after I publish to PBI Service. I also have put text slicer in my report. It seems not to be related to SQL but related to gateway. Could you please check the on premise gateway status is good as below?
    BTW, I’ve saw similar problem in other posts, and the problem is resolved by manually cleaning the registry of all PBI references after an uninstall, and then reinstalled the gateway. You can also have a try.
    
    Best Regards,
    Herbert

Related content