Forum Discussion

Marcus_E's avatar
Marcus_E
Icon for Helper I rankHelper I
6 years ago
Solved

Multiple measure AND conditions

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.

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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")

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Marcus_E
      Icon for Helper I rankHelper I
      6 years ago

      Thank you, both of these solutions work perfectly.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

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