Tabels connection

[TomerIwanir1]

Hi All,

I have two tabels connected with one to many connection, column "Reference" from table "postingline" connected to column "TR_gl_ref" from the "TR hist" table (reference is the one, tr_gl_ref is the many).

When im putting the two toghter on table visual it seems fine (pic below. table B):

than im building another table visual which contain the "Reference" column (pic below,table A)

Now, im clicking on a specific cell at the reference column at table A (IC241210000974) and table B data is disaapearing (pic below):

I dont understand why its disappearing and not showing me filtered date according to my choice at table A.

Please help 🙂

[Anonymous]

Hi @TomerIwanir1 ,

According to your statement, I think you have connect two tables with one to many relationship.

Are there only two tables in your data model? Here I create a sample file and it works well.

Please share a sample file with us. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Use Performance Analyzer to get a copy of the query which is run for the filtered visual, and run it inn DAX Query View. That will show you which columns are being used and exactly what filters are being applied. That may help you figure out why you are seeing the behaviour you are seeing.

[TomerIwanir1]

Did it but not seeing what's driving it

DEFINE

    VAR __DS0FilterTable =

        FILTER(

            KEEPFILTERS(VALUES('Dates'[date])),

            AND('Dates'[date] >= DATE(2024, 10, 1), 'Dates'[date] < DATE(2024, 12, 18))

        )

    VAR __DS0FilterTable2 =

        TREATAS({5301313}, 'GL table'[GLCode])

    VAR __DS0FilterTable3 =

        TREATAS(

            {("6213138", DATE(2024, 10, 13), "RCT-PO 136982", "IC241013000032")},

            'Postline query'[PostingOriginDocument],

            'Dates'[date],

            'Postline query'[PostingLineText],

            'Postline - Posting originreference'[PostingOriginReference]

        )

    VAR __DS0Core =

        SELECTCOLUMNS(

            KEEPFILTERS(

                FILTER(

                    KEEPFILTERS(

                        SUMMARIZECOLUMNS(

                            'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],

                            'Postline - Posting originreference'[PostingOriginReference],

                            __DS0FilterTable,

                            __DS0FilterTable2,

                            __DS0FilterTable3,

                            "CountRowsTR_Hist__CST_ADJ___WO_CLOSE_after_Oct_24", COUNTROWS('TR_Hist, CST-ADJ & WO-CLOSE after Oct 24')

                        )

                    ),

                    OR(

                        NOT(ISBLANK('TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref])),

                        NOT(ISBLANK('Postline - Posting originreference'[PostingOriginReference]))

                    )

                )

            ),

            "'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref]", 'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],

            "'Postline - Posting originreference'[PostingOriginReference]", 'Postline - Posting originreference'[PostingOriginReference]

        )

    VAR __DS0PrimaryWindowed =

        TOPN(

            501,

            __DS0Core,

            'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],

            1,

            'Postline - Posting originreference'[PostingOriginReference],

            1

        )

EVALUATE

    __DS0PrimaryWindowed

ORDER BY

    'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],

    'Postline - Posting originreference'[PostingOriginReference]

[johnt75]

Does the 'Postline query' table have a relationship, either direct or indirect, to 'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'? What about the date table ?

Try running the below in DAX Query View

EVALUATE
CALCULATETABLE (
    'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24',
    TREATAS (
        { ( "6213138", DATE ( 2024, 10, 13 ), "RCT-PO 136982", "IC241013000032" ) },
        'Postline query'[PostingOriginDocument],
        'Dates'[date],
        'Postline query'[PostingLineText],
        'Postline - Posting originreference'[PostingOriginReference]
    )
)

This is replicating the filters created by selecting a row in the other visual. If it returns no rows then the problem comes from one of those 4 filters.

[TomerIwanir1]

Does the 'Postline query' table have a relationship, either direct or indirect, to 'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'? What about the date table ?

yes,  connected

This is replicating the filters created by selecting a row in the other visual. If it returns no rows then the problem comes from one of those 4 filters.

yes, I'm getting rows so really not sure whats going on

[johnt75]

The only other thing I can think to suggest is to play around with

DEFINE
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'Dates'[date] ) ),
            AND (
                'Dates'[date] >= DATE ( 2024, 10, 1 ),
                'Dates'[date] < DATE ( 2024, 12, 18 )
            )
        )
    VAR __DS0FilterTable2 =
        TREATAS ( { 5301313 }, 'GL table'[GLCode] )
    VAR __DS0FilterTable3 =
        TREATAS (
            { ( "6213138", DATE ( 2024, 10, 13 ), "RCT-PO 136982", "IC241013000032" ) },
            'Postline query'[PostingOriginDocument],
            'Dates'[date],
            'Postline query'[PostingLineText],
            'Postline - Posting originreference'[PostingOriginReference]
        )

EVALUATE
SUMMARIZECOLUMNS (
    'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],
    'Postline - Posting originreference'[PostingOriginReference],
    __DS0FilterTable,
    __DS0FilterTable2,
    __DS0FilterTable3,
    "CountRowsTR_Hist__CST_ADJ___WO_CLOSE_after_Oct_24", COUNTROWS ( 'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24' )
)
ORDER BY
    'TR_Hist, CST-ADJ & WO-CLOSE after Oct 24'[trgl_gl_ref],
    'Postline - Posting originreference'[PostingOriginReference]

seeing what you get initially, and then maybe remove the filters from SUMMARIZECOLUMNS one at a time until you can identify what breaks.

I'll shortly be signing off until the New Year but I hope you manage to get it sorted.