Forum Discussion

Tripb44's avatar
Tripb44
Icon for Helper II rankHelper II
4 years ago
Solved

Filter context for measure



I have a measure that I need to add some logic to which would write: If a quote has an order date then return blank.  How would I add that extra logic in to my below measure?  Thanks. 
(1.2) =
var _date = SELECTEDVALUE('Date'[Date])
var _avgquote = AVERAGEX(Cycletime,Cycletime[quote_total])
return
IF(HASONEVALUE(Cycletime[QuoteNum]),
_avgquote,
SUMX(filter(Cycletime,Cycletime[EntryDate]>_date),_avgquote))

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    Tripb44 

    Not sure how your model and the visual are  setup, can you try the following:

    m =
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR __t =  FILTER ( Cycletime , Cycletime[Order Date]  = BLANK () )    
    
VAR _avgquote =
    AVERAGEX ( __t, Cycletime[quote_total] )
RETURN
    IF (
        HASONEVALUE ( Cycletime[QuoteNum] ),
        _avgquote,
        SUMX ( FILTER ( __t, Cycletime[EntryDate] > _date ), _avgquote )
    )

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Tripb44 

    Can you try this measure ?

    Measure = 
var _date = SELECTEDVALUE('Date'[Date])
var _avgquote = AVERAGEX(Cycletime,Cycletime[quote_total])
return
IF(
    NOT(ISBLANK (Cycletime[order date]) ) &&HASONEVALUE(Cycletime[QuoteNum]),
    _avgquote,
    SUMX(filter(Cycletime,Cycletime[EntryDate]>_date),_avgquote)
)
    • Tripb44's avatar
      Tripb44
      Icon for Helper II rankHelper II
      4 years ago

      It is not allowing me to select OrderDate from the table for some reason? 

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        Tripb44 

        Not sure how your model and the visual are  setup, can you try the following:

        m =
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR __t =  FILTER ( Cycletime , Cycletime[Order Date]  = BLANK () )    
    
VAR _avgquote =
    AVERAGEX ( __t, Cycletime[quote_total] )
RETURN
    IF (
        HASONEVALUE ( Cycletime[QuoteNum] ),
        _avgquote,
        SUMX ( FILTER ( __t, Cycletime[EntryDate] > _date ), _avgquote )
    )