Forum Discussion

ArturasT's avatar
ArturasT
New Member
6 years ago
Solved

Matching single transactions when multiple matches occur

Hey all,   First time posting on these forums, so apologies if I end up doing anything wrong 😐    I have a single table from my accounting software with all of my company's issued invoices and r...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi ArturasT ,

     

    We can insert an index column in table 2 and achieve that by DAX.

    Table 2 =
VAR k =
    ADDCOLUMNS (
        'Table (2)',
        "filter",
        VAR ind = 'Table (2)'[Index]
        VAR sn =
            CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[Invoice ID] ),
                FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Index] <= ind )
            )
        VAR no =
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Document ID] ),
                FILTER (
                    'Table',
                    'Table'[Document type] = "Payment"
                        && 'Table'[Invoice date] = DATE ( 2019, 10, 01 )
                )
            )
        VAR index2 = ( sn - 1 ) * ( no + 1 ) + 1
        RETURN
            IF ( sn <= no && 'Table (2)'[Index] = index2, 1, BLANK () )
    )
RETURN
    FILTER ( k, [filter] = 1 )

     

    For more details, please check the pbix as attached.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi ArturasT ,

 

We can insert an index column in table 2 and achieve that by DAX.

Table 2 =
VAR k =
    ADDCOLUMNS (
        'Table (2)',
        "filter",
        VAR ind = 'Table (2)'[Index]
        VAR sn =
            CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[Invoice ID] ),
                FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Index] <= ind )
            )
        VAR no =
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Document ID] ),
                FILTER (
                    'Table',
                    'Table'[Document type] = "Payment"
                        && 'Table'[Invoice date] = DATE ( 2019, 10, 01 )
                )
            )
        VAR index2 = ( sn - 1 ) * ( no + 1 ) + 1
        RETURN
            IF ( sn <= no && 'Table (2)'[Index] = index2, 1, BLANK () )
    )
RETURN
    FILTER ( k, [filter] = 1 )

 

For more details, please check the pbix as attached.