need help with direct query, visualization

[Anonymous]

I have two sql queries - one that has order no, shipped qty and pending qty and the second sql query has order no and no.of days the order is on hold, i need a visualization that has all information from query 1 and just days of hold on table 2. the order no's are uneven - since not all orders have hold, how can i create a visualization for this? i tried left join - it reduces the number of rows affected - i need to have all the order no's and only query 1 has that information, can i append the queries and remove duplicates (how to remove duplicates) Is there another way to solve this.

question 2:

is it necessary to have a on the premises data gateway application for publishing the desktop content to power bi web?

conditional formatting is not working on power bi desktop and web

[Anonymous]

Hi,

First of all what's the requirement to use direct query? are you reporting on real time data? You'll not get all the features in PBI when using Direct Query. If possible, limit the number of rows returned from SQL and do the merge in SQL itself. You can use left Join which will bring all the data from 1st table and matching data from second table, non matching records you can remove using creating a filter using Dax. Try to keep your model simple as possible.

All the best.

LW

[Anonymous]

I am working on real time data and i need to use a direct query, do you sugest that i write a sql code and perform an outer join there and then perform visulisations on power bi?

[Anonymous]

Push back all the processing to the source( direct query relies on server performance as well, so make sure its sufficient enough to cater your requirements ), use a stored procedure to calculate in server side or use a view. 

Direct query only to be used when you need real time analytics and large data volume where local RAM cannot handle. Use import mode and see how it goes.

Thanks

[Anonymous]

@Anonymous ,

I have one more question, right now am using a SQL code – where am bringing in all the order numbers(the reason why I have about a billion rows), in a direct query – can I customize the SQL code? I can use the SQL query to one particular order number, but I need to be able to look for all order numbers, instead of going to the advanced editor and changing the order number, is there any other way I can work on this? If so how?

[Anonymous]

@Anonymous 

Hi,

Try to aggregate the data that you're bringing into Power BI as much as possible by using filterations. You can customise the SQL code in direct query but Its not best practice. If you have 10 work orders and you need to report only 1 then bring only one. 

• Push calculated columns and measures to the source where possible – the closer they are to the source, the higher the likelihood of performance.
• Optimize! Understand the execution plans for your queries, add indices for commonly filtered columns, etc.

If you have billion records to be imported then direct query is the only option but make sure your server performance is good without any Network latency.

If you're more specific about what you're trying to achieve or visualisation that you're trying to do would be helpful. 

Finding a match can also be achieved via Lookup Dax function. 

[Anonymous]

@Anonymous ,

I have one more question, right now am using a sql code – where am bringing in all the order numbers(the reason why I have about a billion rows), in- direct query – can I customize the sql code? I can use the sql query to one particular order number, but I need to be able to look for all order numbers, instead of going to the advanced editor and changing the order number, is there any other way I can work on this? If so how?

[v-xuding-msft]

Hi @Anonymous ,

I can’t reproduce your issue. At least in theory, the way of left anti join should be worked. The following is my sample using two ways to implement. you can reference to modify your data. If you are still failed, please post some sample data and the expected output.

Question 1:

1. The first way is using left join in Power query.

These are my two tables.

Choose the column of order no in two tables.  Note :  the join kind should be changed according to your query1. If it is in the position of table1, then choose the left join. If not ,choose the right join.

1. The second way is managing a one-to-one relationship between two tables in report view. The visualization I used is a table.

Question 2 :

If you get data using direct query mode , in most cases, you need to install gateway. And there is a document you can reference.

Question 3:

Please post some data and screenshot about the issue of conditional formatting, then we can help you quickly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xuding-msft ,

I tried following the steps like you showed, when i try a left outer join , with 2lakh records of 1 million matching, but however, the joined table doesn't  get displayed , and when i tried managing a one to one relationship between the two, it gives me the error as shown in the picture

there are two sql queries than am using, for direct query - each query of 400 lines

Can you please help me out with this?