Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Can I use a conditional filter expression?

I'm trying to create a dynamic measure that can be configured using a drop-down. This way someone can choose the period over which to sum sales. I already got this working with Month / MQT / MAT, by ...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago
    Anonymous wrote:
    Unfortunataly it seems I can't use an IF statement like this, since that will result in an error "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression". Do I need to do this differently?

    Both DATESYTD and DATESINPERIOD return tables and unfortunately you can't return a table value from the IF function. So you will need to put your calculate inside the IF so that it is returning a scalar value.

     

    eg.

     

    # Sales = 
IF(
  condition = true;
    CALCULATE(
       [Total Sales], 
       DATESYTD( ... )
  );
  CALCULATE(
       [Total Sales], 
       DATESINPERIOD( ... )
  );
)
d_gosbell's avatar
d_gosbell
Super User
7 years ago
Anonymous wrote:
Unfortunataly it seems I can't use an IF statement like this, since that will result in an error "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression". Do I need to do this differently?

Both DATESYTD and DATESINPERIOD return tables and unfortunately you can't return a table value from the IF function. So you will need to put your calculate inside the IF so that it is returning a scalar value.

 

eg.

 

# Sales = 
IF(
  condition = true;
    CALCULATE(
       [Total Sales], 
       DATESYTD( ... )
  );
  CALCULATE(
       [Total Sales], 
       DATESINPERIOD( ... )
  );
)