Forum Discussion

River's avatar
River
Icon for Helper IV rankHelper IV
4 years ago
Solved

Filter inside treatas, is it possible

Hi Friends,   It's always a struggle with DAX:-)   I have a dropdown visual with some hard code text as filter. it will filter out an invoice table first, then, I am going to use 'TreatAs' to joi...
  • smpa01's avatar
    smpa01
    4 years ago

    River  is it meeting your expectation?

    _newMeasure =
    CALCULATE (
        - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ),
        TREATAS (
            SUMMARIZE (
                FILTER (
                    'Invoice',
                    CONTAINSSTRING ( Invoice[INVOICE], SELECTEDVALUE ( InvoiceFilter[Filter] ) )
                ),
                'Invoice'[AccountNum],
                'Invoice'[Voucher],
                'Invoice'[DataAreaId],
                'Invoice'[Partition]
            ),
            'Invoice Paid'[AccountNum],
            'Invoice Paid'[LastSettleVoucher],
            'Invoice Paid'[DataAreaId],
            'Invoice Paid'[Partition]
        )
    )
    

     

     

    Recon and let us know.

  • PaulDBrown's avatar
    4 years ago

    Try:

    Amount Paid FILTER =
    VAR InvTable =
        SUMMARIZE (
            FILTER (
                Invoice,
                LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] )
            ),
            'Invoice'[AccountNum],
            'Invoice'[Voucher],
            'Invoice'[DataAreaId],
            'Invoice'[Partition]
        )
    RETURN
        CALCULATE (
            - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ),
            TREATAS (
                InvTable,
                'Invoice Paid'[AccountNum],
                'Invoice Paid'[LastSettleVoucher],
                'Invoice Paid'[DataAreaId],
                'Invoice Paid'[Partition]
            )
        )
    

     

  • smpa01's avatar
    smpa01
    4 years ago

    River  create this measure and put it as filter

    _filter = CALCULATE(MAX(Invoice[INVOICE]),FILTER(VALUES(Invoice[INVOICE]),CONTAINSSTRING(Invoice[INVOICE],SELECTEDVALUE(InvoiceFilter[Filter]))))

     

     

  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Sorry, I'm not sure what you mean. The measure returns the sum for the matching rows from both tables defined in TREATAS. There are invoices which haven't been paid, so they will show up if you include the invoice number in the table, but the measure returns blank of course.

    You can try filtering the table using the following as a filter in the filter pane and setting the value to 1:

    Filter Table =
    COUNTROWS (
        FILTER (
            Invoice,
            LEFT ( Invoice[INVOICE], 5 ) IN VALUES ( InvoiceFilter[Filter] )
        )
    )
    

     

    Which returns the following:

     

    For account "5", for example, there are no rows in the paid table matching the criteria established in the TREATAS function.

    I've attached the file for your reference

  • River's avatar
    River
    4 years ago

    It worked, Paul and you basically come to similar approach.

     

    Guys, really appreciate it.