Forum Discussion
DAX Calculation for a Calculated Column - Not Able to Reference Column in another table
I am building a PowerBI report. I have two tables that I am using a for a calculated column called Security Audit (knowledge date field) and SecMasterDailyView (approval date field). I want to write a statement that checks if knowledge date is after the approval.
Hi Anonymous ,
I created some data:
Security Audit：
SecMasterDailyView：
You can use functions such as MINX, MAXX, SUMX to get the columns of another table:
https://learn.microsoft.com/en-us/dax/maxx-function-dax
https://learn.microsoft.com/en-us/dax/minx-function-dax
Here are the steps you can follow：
1. Create calculated column.
Column = var _date= MINX(FILTER(ALL(SecMasterDailyView),'SecMasterDailyView'[ID]=EARLIER('Security Audit'[ID])),[approval date field]) return IF( _date>'Security Audit'[knowledge date field],"yes","no")2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Anonymous , related only work from 1 to many side.
From many to one side you can use
maxx(relatedtable(SecMaterdailyView), [Approval Date])
or a column like
maxx(filter(SecMaterdailyView,SecMaterdailyView[ID] = 'Security Audit'[ID]) ,SecMaterdailyView [Approval Date)
refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8amitchandak When I put the column on the one to many side, it still didn't work. How would I rewrite the calculated column then?
Hi Anonymous ,
I created some data:
Security Audit：
SecMasterDailyView：
You can use functions such as MINX, MAXX, SUMX to get the columns of another table:
https://learn.microsoft.com/en-us/dax/maxx-function-dax
https://learn.microsoft.com/en-us/dax/minx-function-dax
Here are the steps you can follow：
1. Create calculated column.
Column = var _date= MINX(FILTER(ALL(SecMasterDailyView),'SecMasterDailyView'[ID]=EARLIER('Security Audit'[ID])),[approval date field]) return IF( _date>'Security Audit'[knowledge date field],"yes","no")2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
