Forum Discussion
conditional join from sql server to power bi in direct query mode
Hi, I am importing tables from SQL Server into Power BI in Direct Query mode. Between two tables in SQL Server there is a conditional join, “many to one” , with this query:
Sales.Promo=DER.R6A and
Sales.Data>=DER.F6A and
Sales.Data<=DER.F6B
Sales and DER are the tables. Promo, Data, R6A,F6A,F6B are the columns.
How to import in Direct Query mode these tables with this type of join?
Thanks!
6 Replies
AnushaSriResolver II
Hi,
You can do the following steps to create a valid conditions.
step1: Join your tables using Promo and R6A columnsstep2: create a measure (we refer this as a validation measure) such that calculate distinct count of uniquekey of sales table and filter your conditions something like this
measure = Calculate(Distinctcount(sales.uniqueid), Sales.Data>=DER.F6A,
Sales.Data<=DER.F6B)step3: Use this measure in Visual level filters / page level fiters where ever is required as measure >=1.
Please accept this as solution and give kudos if it works for you.
Thanks in advance.Wouldn't it be possible to create the conditional join directly in the import in DIrect Query by writing the SQL statement? I'm asking because by importing the tables without joins, then power bi does not allow “Many to One” joins from Sales to DER. But only allows me a many to many relationship. How can I solve this? Many thanks
- AnushaSri
Yes, You can connect to sql data by writing a sql query in direct query mode.
You can add you your sql query under advanced option