Forum Discussion
Create a Calculated Column from Different Tables
Based on my understanding, it depends on the relationship.
If 1:1, below formula should work.
calculated column = table1[X] - RELATED ( table2[Y] )
If 1:many, the following formula should work.
calculated column = oneTable[X] - CALCULATE ( SUM ( manyTable[Y] ), ALLEXCEPT ( manyTable, Table10[linkedCol] ) )
We have two tables with a relationship of many : 1 between Table1and Table2.
We are getting errors for below derive column for filter condition : Table1[Web Activity Date] <= RELATED(Table2[Lead Created Date])
------------------
# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Web Activity Date] <= RELATED(Table2d[Lead Created Date]))
Hi,
In Table1, write the RELATED function as a calculated column formula and give a title to that column as Lead date. Write another calculated column column called test with this formula =Table1[Web Activity Date]<=Table1[Lead date] . Revise your measure to:
# Distinct Forms Filled by Net New Lead = CALCULATE(DISTINCTCOUNT(Table1[Form ID]),v_rpt_omniture[Post Visid High Low] <> "10"
&& Table1[Form Completions] = "1"
&& Table1hit Source] = "1"
&& Table1[Exclude Hit] = "0"
&& Table1[Test] = TRUE())
Thank you Ashish_Mathur for the solution. It is helpful.
Just want to confirm : Are we saying we cannot use related table with multiple filter condition in CALCULATE function?
You are welcome. I prefer using a calculated column formula because it simplifies the measure and allows me to go back to the table and apply filters, if i ever have to.