Forum Discussion

Tripb44's avatar
Tripb44
Helper II
4 years ago
Solved

Measure filter context

I have a sql below with the date selected to mimic the selected value in my dax measure below.  I am not getting and returns for my measure when I have a selected value for the current date which outputs 11/02/2021.  I have the same filter logic in both the sql and dax but only the sql is returning the correct output. Thanks for the help.


 




 

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Tripb44 ,

    Try to modify the measure like this:

    Average Invoice =
VAR _date =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR _t3 =
    FILTER (
        'Cycletime_Invoice_Date',
        'Cycletime_Invoice_Date'[InvoiceDate] <= _date
            && (
                'Cycletime_Invoice_Date'[ClosedDate] = BLANK ()
                    || 'Cycletime_Invoice_Date'[InvoiceDate] >= _date
            )
    )
VAR _avginvoice =
    AVERAGEX ( _t3, 'Cycletime_Invoice_Date'[InvoiceAmt] )
VAR tab =
    ADDCOLUMNS ( _t3, "A", _avginvoice )
RETURN
    IF (
        HASONEVALUE ( 'Cycletime_Invoice_Date'[InvoiceNum] ),
        _avginvoice,
        SUMX ( tab, [A] )
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Tripb44 , iN return, simply try sumx without hasonevalue condition.

     

    Also, date table should be an independent table in this case

    • Tripb44's avatar
      Tripb44
      Helper II
      4 years ago

      Hi thanks for the reply, please see below comments.
      I am needing for example below, cirlced is invoice #171703. 


      Here is the raw data for  invoice#171703 and Selected value for reference:


      Trouble is when I select for example November 2021 for the selected value, invoice #171703 is not included in the measure as an output.  The relationship to the date table is "InvoiceDate" shown above.  I am assuming the selected value is filtering out this example as the selected value is in November 2021 and the relationship to the date table for this example is October 2021?  Note for example, if the selected value is October 2021, then the correct output is displayed for the example in the measure also referenced below.  

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi Tripb44 ,

        Try to modify the measure like this:

        Average Invoice =
VAR _date =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR _t3 =
    FILTER (
        'Cycletime_Invoice_Date',
        'Cycletime_Invoice_Date'[InvoiceDate] <= _date
            && (
                'Cycletime_Invoice_Date'[ClosedDate] = BLANK ()
                    || 'Cycletime_Invoice_Date'[InvoiceDate] >= _date
            )
    )
VAR _avginvoice =
    AVERAGEX ( _t3, 'Cycletime_Invoice_Date'[InvoiceAmt] )
VAR tab =
    ADDCOLUMNS ( _t3, "A", _avginvoice )
RETURN
    IF (
        HASONEVALUE ( 'Cycletime_Invoice_Date'[InvoiceNum] ),
        _avginvoice,
        SUMX ( tab, [A] )
    )

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.