cancel
Showing results for 
Search instead for 
Did you mean: 

Error retrieving data in powerBI

Setup:

Power BI 

Connected to Spark SQL Data Source

 

So I used spark SQL data source to connect to power bi application.

On the receiving end on the spark thrift server, I parse the query to optimize it further.

The issue I am facing is when I apply a filter visual level from the dimension dropdown

The query I am receiving on my end is 

select top 100001 group1, group2 from (sql * from table where filter in (?,?)) group by group1,group2

I cannot understand why I am getting ? in filter values even when I have selected correct lookup

It works when there is only a filter value applied.

 

I am attaching the logs from my end

 

 

 

 

ExecuteDirect","ResourceKind":"Spark","ResourcePath":"","cliservice","HostProcessId":"324","CommandText":"select top 1000001\r\n    `dimension`,\r\n    sum(`measurement`) as `C1`\r\nfrom \r\n(\r\n    select `<dimension>`\r\n    from `SPARK`.`table`\r\n    where `group` in (?, ?)\r\n) as `ITBL`\r\ngroup by `group`","ParameterCount":"2","Skip":"0","Take":"Infinite","PageRowCount":"0","ProductVersion":"2.79.5768.1082 

 

 

 

The query which is working 

 

 SELECT SUM(C_43) AS C_4331  FROM (SELECT C_61756469656e63655f61637469766974795f61756469656e63655f6163746976697479.`dimension1` AS C_43  FROM `table`  C_61756469656e63655f61637469766974795f61756469656e63655f6163746976697479 WHERE C_61756469656e63655f61637469766974795f61756469656e63655f6163746976697479.`dimension2` IN ('val1','val2','val3')  C_4954424c  

 

 

Status: New
Comments
Community Support

@Anike ,

 

Do you mean when you apply only one value in visual level filter, it works well, but when you apply multiple values, error occurs?

 

Regards,

Jimmy Tao

Regular Visitor

Hi @v-yuta-msft,

 

Thanks for responding. 

1. When I select multiple values in the visual level filter with dimensions and measurements, I get this error. 

2. When I select multiple values in the visual level filter with only measurements, it works. 

 

When 1 is called, the SQL which I receive from the PowerBI app is

Select top 100001 dimension1, sum(measurement1) from table where filter1 in (?,?) group by dimension1

 

I don't understand why are the values not passed in the filter.

 

When 2 is called, the SQL which I receive from the PowerBI app is

Select sum(measurement1)from table where filter in  ("val1","val2") 

 

I am able to run this. This works perfectly.

 

I can attach a screenshot and debug/trace logs from the PowerBI app if you want.

 

Regards