Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiple Filters for Calculate

The goal is to create a measure that will calculate the number of transactions except those that are greater than 60 days ( > 60 ) and does not have an "Overdue - Outstanding" status. Ultimately, everything except the 2 criteria noted above is needed. My current output is not correctly reflecting all of the other results that are needed. For context, below you will find the following: Current Measure / Expected Output Sample Data / Current Output Sample Data. 

 

Your support is greatly appreciated. 

 

Current Measure >> My assumption is that I can just add && to the filter context and it will be included for the specific item that I want to exclude but the the current output is far from the expected output

 

CALCULATE(
    [Transaction Count]
    ,FILTER(
        'Transactions'
        ,'Transactions'[Complete vs Outstanding Status] <> "Overdue - Outstanding" && 'Transactions'[# of Days Since Last Transaction] > 60
    )
)

 

Expected Output Sample Data

Month YearCompletedNot Overdue - OutstandingOverdue - Outstanding
May 20225519202
April 202290 505
March 202276 65
February 202224  
January 202265  
December 202119  

Current Output Sample Data >> Note the difference between vs. Expected Output for Months: May / April / March (currently no output) 

Month YearCompletedNot Overdue - OutstandingOverdue - Outstanding
May 2022   
April 2022   
March 202238  
February 202224  
January 202265  
December 202119  
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    I solved my own problem by updating the measure to replace && with || 

     

    I am still confused on how this worked because the parameters require that both criteria are needed for this to be applied yet in PBI, the expected output was found using OR ( || ). If anyone can explain to me how or why this is the case, it will be appreciated. Regardless, happy to find the solution. 

     

    CALCULATE(
    [Transaction Count]
    ,FILTER(
        'Transactions'
        ,'Transactions'[Complete vs Outstanding Status] <> "Overdue - Outstanding" || 'Transactions'[# of Days Since Last Transaction] < 60
    )
)

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I solved my own problem by updating the measure to replace && with || 

     

    I am still confused on how this worked because the parameters require that both criteria are needed for this to be applied yet in PBI, the expected output was found using OR ( || ). If anyone can explain to me how or why this is the case, it will be appreciated. Regardless, happy to find the solution. 

     

    CALCULATE(
    [Transaction Count]
    ,FILTER(
        'Transactions'
        ,'Transactions'[Complete vs Outstanding Status] <> "Overdue - Outstanding" || 'Transactions'[# of Days Since Last Transaction] < 60
    )
)