locked
FILTER with 2nd Condition RRS feed

  • Question

  • Hi,

    I'm trying to FILTER the Journal Lines table firstly by Account Code = "860".

    Following, I want iterate over my filtered rows and for each row check the corresponding side of the Journal (Via Journal ID) in the same table (All rows contain a Journal ID). If that corresponding Journal ID has Account Code "680", then keep the Account Code = "860" in my filtered table, otherwise discard it.  

    I have had a go below, but the logic is a bit out of my league and was hoping somebody could help.

    Rounding = 

    CALCULATE(
                      SUMX( 'Journal Lines', 'Journal Lines'[Net Amount]),
                      FILTER('Journal Lines', 'Journal Lines'[Account Code] = "860"  
                     && NOT ISEMPTY ( CALCULATETABLE ( 'Journal Lines',
                         FILTER('Journal Lines', 'Journal Lines'[Journal ID] = 'Journal Lines'[Journal ID] && 'Journal Lines'[Account Code] = "680")) )

    ))

                         
    Thursday, March 15, 2018 10:55 AM

Answers

  • Hi Cameron1900,

    Thanks for your question.

    In this scenario, please try below DAX formula:
    Rounding =
    IF (
        COUNTROWS (
            FILTER (
                'Journal Lines',
                'Journal Lines'[Journal ID] = MAX ( 'Journal Lines'[Journal ID] )
                    && 'Journal Lines'[Account Code] = "680"
            )
        )
            > 0,
        SUMX (
            FILTER (
                'Journal Lines',
                'Journal Lines'[Journal ID] = MAX ( 'Journal Lines'[Journal ID] )
                    && 'Journal Lines'[Account Code] = "860"
            ),
            'Journal Lines'[Net Amount]
        )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 16, 2018 2:24 AM

All replies

  • Hi Cameron1900,

    Thanks for your question.

    In this scenario, please try below DAX formula:
    Rounding =
    IF (
        COUNTROWS (
            FILTER (
                'Journal Lines',
                'Journal Lines'[Journal ID] = MAX ( 'Journal Lines'[Journal ID] )
                    && 'Journal Lines'[Account Code] = "680"
            )
        )
            > 0,
        SUMX (
            FILTER (
                'Journal Lines',
                'Journal Lines'[Journal ID] = MAX ( 'Journal Lines'[Journal ID] )
                    && 'Journal Lines'[Account Code] = "860"
            ),
            'Journal Lines'[Net Amount]
        )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 16, 2018 2:24 AM
  • Thank you Willson
    Saturday, March 17, 2018 3:37 AM