Forum Discussion

Diego-mx's avatar
Diego-mx
Advocate I
7 years ago

Combine TREATAS filters in FILTER function

In a CALCULATE expression I can add different filters with commas. 

 

TotalSells = CALCULATE( SUM(Merchandise[prices]), 
    Merchandise[was_sold], 
    TREATAS( VALUES(Calendar[date]), Merchandise[sold_date])
)

Now I need to use the filters inside an X-function, from which the parameter changes. 

QuickSells = AVERAGEX( 
    FILTER(Merchandise, 
        [was_sold], 
        TREATAS( VALUES(Calendar[date]), Merchandise[sold_date])), 
    Merchandise[days_to_sell] <= 10
)

This throws an error since FILTER only takes one filter.  
If they were all logical, I could do mix them with & operators.  
Since I have TREATAS, it gets more difficult. 
Does someone know how to do this?

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Diego-mx,

     

    I can't get the calculate logic from your formula, but I think it is impossible to modify teratas function to add conditions into function.

     

    Based on my understanding, treatas function is similar as 'in' operate. Maybe you can try to use following formula if it suitable for your requirement:

    QuickSells =
AVERAGEX (
    FILTER (
        ALLSELECTED ( Merchandise ),
        Merchandise[was_sold] = TRUE ()
            && Merchandise[sold_date] IN VALUES ( Calendar[date] )
            && Merchandise[days_to_sell] <= 10
    ),
    Merchandise[prices]
)

    Regards,

    Xiaoxin Sheng

    • Diego-mx's avatar
      Diego-mx
      Advocate I
      7 years ago

      The logic is that I want to find what proportion where sold in less than 10 days. 
      So the condition [sold_days] <= 10, gets turned into 1 and 0 and averaged.  

      Using ... IN VALUES(Calendar[date]) was helpful, but now the logical-to-numeric is not working.  

      I'll keep trying. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI Diego-mx,

         

        Can you please share some sample data with expect result to help us clarify your scenario?

         

         

        Regards,

        Xiaoxin Sheng