Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Query vs Table as data source for report

My original report was using a table as the datasource, however, I needed to change the datasource to a query.  for some reason when I used the query the report does not display correctly as a bar chart, it only displays a single bar instead of the past 14 days with of values (displaying multiple bars)  The difference in the query is that it is now contains a left join to the Current Account table.  The data source for the query is a SQl Query that limits the results based on the following query:

 

 

 

SELECT [DeviceID]
      ,[AccountNumber]
      ,[DeviceLabel]
      ,[DeviceFormat]
      ,[DeviceProvisioned]
      ,[DeviceReturned]
      ,a.[AzureBlobInsertDate]
  FROM [DBO].[CurrentDevices] a /*AzureProd*/
   	left join [DBO].[CurAccounts] b 
	on a.accountnumber =b.ID
WHERE (isnull(b.tags,'') not like '%test%'   
		AND isnull(b.tags, '') not like '%pqa%')   
		AND b.type='pay'   
		AND b.category='residential'

Based on Table

 

 

 

 

 

 

 

 

 

 

 

Why does the Table return the correct format for the chart but the query does not?

 

Thanks,

 

Karen

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    When changing the data source of a Power BI you need to assure that the final result is the exact same one on both source, meaning that you need to have the same column names and the same formats, on the last step of the query that way your  visuals will no be broken.

     

    Does the change for the SQL query is giving the exact same result as the table on the query editor?

     

    Regards,

    MFelix

  • Anonymous's avatar
    Anonymous
    7 years ago

    Thank you for your interest and input, however, the problem seemed to resolve itself, when I reverted back to an earlier version of the PBI.

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous ,

     

    When changing the data source of a Power BI you need to assure that the final result is the exact same one on both source, meaning that you need to have the same column names and the same formats, on the last step of the query that way your  visuals will no be broken.

     

    Does the change for the SQL query is giving the exact same result as the table on the query editor?

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the suggestion, however, both table and query contain 13 fields (some are measures), the only difference is the field names in query are different, due to I do not want to remove the table.  The function of the measure are repointed to the query name, but is the exact same as the tables.

       

      Do you have any other suggestions?

       

      Karen

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        If you have both sources on your file, then what is happening is that you are using the columns from the Table as information for the measures that are related to the query, since (i'm assuming) you don't have any relationship between both the result is the sum of all the values in the measures.

         

        Check the Visual to see if you are picking up the columns for the axis, legend and so on from the correct source in this case the SQL query and not the Table source.

         

        Regards

        MFelix