Get Value from Table having Implicit Relationship

[Anonymous]

Hello All,

I have a table with below relationship,

Now here what I want to is add a calculated column in Table A. The formula for Calculated Column in Table A will look something like this - 

Column Table A = IF(Table A[DateTime] >= Table B[StartDate] AND Table A[DateTime] < Table B[EndDate], 1, 0)

Now my issue is there is no direct relationship between Table A and Table B but both these tables are related to Date/Calendar table 

[amitchandak · Accepted solution]

@Anonymous , Try like new column

if(isblank(countx(filter('Table B','Table A'[DateTime] >= 'Table B'[StartDate] && 'Table A'[DateTime] < 'Table B'[EndDate]),'Table B'[DateKey])),0,1)

[edhans]

You should wrap the TableB info in RELATED(), but why is table B not inside of Calendar? If it is 1:1, just merge in Power Query and don't load table B at all. I cannot think of a good reason to have a 1:1 relationship in Power BI. 

And if possible, do your columns in Power Query as well as calculated columns have issues. If you share some data, we can assist in getting you what you want inside of PQ with custom column.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum