Multiple measure AND conditions

[Marcus_E]

Hi

I'm trying to eliminate unnecessary slicers on my report page by filtering key outputs using measures but am struggling combining multiple AND conditions within the measure.

The scenario is:

Table1: [Transaction_Name][Quantity]

Table2: [Resource_Type][Resource_Description]

I've tried writing the following measure that will output a sum of the [Quantity] column where a) the Quantities are greater than 0 AND the [Resource_Description] in a separate linked table = "Contractors". 

This is what I've tried:

Measure (Quantity Total) =

CALCULATE (

SUM (Transaction_Name[Quantity]) ,

FILTER(Transaction_Name, Transaction_Name[Quantity] > 0) &&

FILTER(Resource_Type, [Resource_Type][Resource_Description] = "Contractors"))

Unfortunately I keep getting an error: 'A function 'FILTER' has been used in a TRUE / FALSE expression that is used as a table filter expression'. Have tried a number of different iterations of the expression but can't get it to work with multiple conditions.

I know it should be straight forward but I just can't work out how to apply filters from different tables.

Anyone with any suggestions would be appreciated. Thanks.

[amitchandak · Accepted solution]

@Marcus_E , Try one of three

Measure (Quantity Total) =
CALCULATE (
SUM (Transaction_Name[Quantity]) ,
FILTER(Transaction_Name, Transaction_Name[Quantity] > 0) ,
FILTER(Resource_Type, Resource_Type[Resource_Description] = "Contractors"))

Measure (Quantity Total) =
CALCULATE (
SUM (Transaction_Name[Quantity]) ,
Transaction_Name[Quantity] > 0 && Resource_Type[Resource_Description] = "Contractors")

Measure (Quantity Total) =
CALCULATE (
SUM (Transaction_Name[Quantity]) ,
FILTER(Transaction_Name, Transaction_Name[Quantity] > 0) , Resource_Type[Resource_Description] = "Contractors")

[Marcus_E]

Thank you, both of these solutions work perfectly.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

Does this work?

Measure (Quantity Total) = CALCULATE(SUM('Transaction_Name'[Quantity]),FILTER('Transaction_Name', 'Transaction_Name'[Quantity]>0),FILTER('Resource_Type','Resource_Type'[Resource_Description] = "Contractors"))

If it does not help, then share the link from where i can download your PBI file.