Conditional join in Power BI

[anileshknpowerb]

Hi All,

I have star schema with 8 Dimension tables and 1 Fact table.All joins are straight forward,except the below one.

Fact_W_AR_BALANCE_F.BALANCE_DT_WID <= Dim_W_MCAL_PERIOD_D_Current_Period.MCAL_PERIOD_END_DT_WID
AND Fact_W_AR_BALANCE_F.MCAL_CAL_WID = Dim_W_MCAL_PERIOD_D_Current_Period.MCAL_CAL_WID
AND Dim_W_MCAL_PERIOD_D_Current_Period.ADJUSTMENT_PERIOD_FLG ='N'
AND Dim_W_MCAL_PERIOD_D_Current_Period.W_CURRENT_MCAL_PERIOD_CODE = 'Current'

Please help me how to it in power BI.

Thanks in Advance.

[amitchandak]

@anileshknpowerb , you can have such; in a measure or when you create a new table.

Refer Measure: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Refer Join: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[anileshknpowerb]

@amitchandak , thanks for your prompt response.

I want it as two separate tables,the reason end user will use for Adhoc reporting.

Currently the above join condition is present in OBIEE which we are planning to implement in POWER BI.

Even i cannot have it in measures,reason i am having one more Date Dimension and the joins looks like below.

Fact_W_AR_BALANCE_F.BALANCE_DT_WID = Dim_W_MCAL_DAY_D_Fiscal_Day.MCAL_DAY_DT_WID AND
Fact_W_AR_BALANCE_F.MCAL_CAL_WID = Dim_W_MCAL_DAY_D_Fiscal_Day.MCAL_CAL_WID AND
Dim_W_MCAL_DAY_D_Fiscal_Day.ADJUSTMENT_PERIOD_FLG = 'N'

Thanks..

[amitchandak]

@anileshknpowerb , for equal join You Concat two or more columns   and create a new column on both sides.

Like on one side concat ADJUSTMENT_PERIOD_FLG  and another side you can only have value N

New columns

Key = Column1 & "-" & Column2 & "-" & Column3

Key = Column1 & "-" & Column2 & "-N"