Forum Discussion
Get Value from Table having Implicit Relationship
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
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)
2 Replies
- edhans
Community Champion
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 QueryHow 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 - amitchandak
Super User
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)
