Forum Discussion

mrlang02's avatar
mrlang02
Frequent Visitor
6 years ago

Exclude Data Points Listed in External Table

Hi. I have the following measure [Measure 2], which calculates the values for Measure 1 based using values in Table, filtered by measures in a disconnected date table ([RefPeriodEnd] and [RefPeriodStart] reference the disconnected date table). 

 

 

Measure2:=CALCULATE(
	[Measure1],
	FILTER(
		ALL(Table),
		Table[Date] <= [RefPeriodEnd]
		&& Table[Date] >= [RefPeriodStart])
		)

 

 

This works fine. 

 

I now need to add one more filter that will allow me to omit data points where values of Table[Datapoint] are in Table2[Datapoint].

Table2 is simply a list of the data points I want to omit.  

 

I have read that the CONTAINS function might accomplish this, but I am having trouble getting the syntax right with the code I already have.  I also tried adding a Calculated Column that returns TRUE or FALSE if the Table1 data point value is in Table 2 and add "&& ALL_Env[FilterOut]=FALSE())" to the filter statement.  But Filter does not allow evaluation on True/False, apparently.  

 

Any suggestions??  Thanks for your help.  

1 Reply

  • mrlang02's avatar
    mrlang02
    Frequent Visitor
    6 years ago

    Solved my own issue, though there is perhaps a better way.  I used the ALLEXCEPT() command, allowing me to still filter by the "FILTEROUT" column which has True/False values.

     

    Measure2:=CALCULATE(
	[Measure1],
	FILTER(
		ALLExcept(Table, Table[FilterOut]),
		Table[Date] <= [RefPeriodEnd]
		&& Table[Date] >= [RefPeriodStart])
		)