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 using the following measure:

 

# Sales = CALCULATE(
     [Total Sales], 
     DATESINPERIOD(
         'Date'[Date];
        MAX('Date'[Date]);
        SELECTEDVALUE(Period[offset]);
        MONTH
    )
)
Now I'm looking to add a YTD. Unfortunataly I can't use the DATESINPERIOD because of the fact that the amount of months changes on regular basis. I'm looking for a way to conditionally choose between DATESYTD & DATESINPERIOD. 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?
 
IF(
   condition = true;
   DATESYTD(....);
   DATESINPERIOD(...);
)
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( ... )
  );
)

1 Reply

  • 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( ... )
  );
)