DAX for Complex joins in Tabular Models

[Anonymous]

Hi I am new to tabular models.

I am trying to implement a complex join. For this I have to use an ID column from the table & deleted = true to connect to the other table.

I am trying to create a calculated column but the syntax is failing. 

Also I just went into SSDT and filtered the "deleted" column on the table but when I make the join deploy the model to DEV SSAS and query it from POWER BI Desktop its bringing back all the values.

What is the best approach to reslolve this and get a complez join to work.

[Nathaniel_C]

Hi @Anonymous ,

If I understand what you are trying to do, there should be an easy solution in Power Query. Would you provide us with a picture of your tables, and what your end product would look like? You can do this with a pbix, or use Excel to dummy up a set of tables, and paste them into your message.

Thanks,

Nathaniel

[Anonymous]

This is what I am able to accomplish with my other BI tool with stratight forward SQL Joins

I am trying to accomplish this with Tabular model in Direct Query Mode and based on some research i am trying to create calculated column and Join which is not working.  

Table1 

ID,NAME,TYPE, ADDRESS

TABLE2 

ID,NAME,TYPE, ADDRESS,DELETED

TABLE3

ID,NAME,TYPE, ADDRESS,DELETED,CURRENT 

Current Join in BI Model SQL 

Current JOIN IN SQL Table1.ID = Table2.ID AND TABLE2.Deleted =0

Current JOIN IN SQL Table1.ID = Table3.ID AND TABLE3.Deleted =0 AND TABLE3.CURRENT = 'Y'

[Nathaniel_C]

Sorry, I don't know the implications of direct query.  I am going to reach out to these @v-lid-msft , @Icey  @v-yuta-msft , @v-yuezhe-msft 
Nathaniel

[Anonymous]

So if not direct query do you have an example of a successful complex join inside a tabular model using a  calculated column?