conditional join from sql server to power bi in direct query mode

[userpien]

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!

[AnushaSri]

Hi,

You can do the following steps to create a valid conditions.

step1: Join your tables using Promo and R6A columns 

step2: 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.

[userpien]

can you write me the exact query I need to write to create this measure? what functions to use and how to nest them? thanks!

[userpien]

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

[userpien]

yes bot what to write there?

second question, in the query you wrote 
measure = Calculate(Distinctcount(sales.uniqueid), Sales.Data>=DER.F6A,
Sales.Data<=DER.F6B)

but how to select DER.F6A? with selectcolumns?
many thanks!

[AnushaSri]

This is the query you can write, and instead of * (unless you want to get all columns from both sales and der tables) write the column names of what ever the columns you need.(like select column1, column2, column3 from.....)

Select
* from 

sales

join DER ON Sales.Promo=DER.R6A and
Sales.Data>=DER.F6A and
Sales.Data<=DER.F6B