Tabels connection

Question

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).&nbsp;When im putting the two toghter on table visual it seems fine (pic below. table B):&nbsp;&nbsp;than im building another table visual which contain the "Reference" column (pic below,table A)&nbsp;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.&nbsp;Please help 🙂&nbsp;&nbsp;&nbsp;

johnt75 · Answer

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.

anonymous · Answer

Did it but not seeing what's driving it&nbsp;DEFINE&nbsp; &nbsp; VAR __DS0FilterTable = &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(VALUES('Dates'[date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND('Dates'[date] &gt;= DATE(2024, 10, 1), 'Dates'[date] &lt; DATE(2024, 12, 18))&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR __DS0FilterTable2 = &nbsp; &nbsp; &nbsp; &nbsp; TREATAS({5301313}, 'GL table'[GLCode])&nbsp; &nbsp; VAR __DS0FilterTable3 = &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {("6213138", DATE(2024, 10, 13), "RCT-PO 136982", "IC241013000032")},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Postline query'[PostingOriginDocument],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Dates'[date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Postline query'[PostingLineText],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Postline - Posting originreference'[PostingOriginReference]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR __DS0Core = &nbsp; &nbsp; &nbsp; &nbsp; SELECTCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZECOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Postline - Posting originreference'[PostingOriginReference],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __DS0FilterTable,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __DS0FilterTable2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __DS0FilterTable3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "CountRowsTR_Hist__CST_ADJ___WO_CLOSE_after_Oct_24", COUNTROWS('TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OR(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT(ISBLANK('TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT(ISBLANK('Postline - Posting originreference'[PostingOriginReference]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "'TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref]", 'TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "'Postline - Posting originreference'[PostingOriginReference]", 'Postline - Posting originreference'[PostingOriginReference]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR __DS0PrimaryWindowed = &nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 501,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; __DS0Core,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Postline - Posting originreference'[PostingOriginReference],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; )EVALUATE&nbsp; &nbsp; __DS0PrimaryWindowedORDER BY&nbsp; &nbsp; 'TR_Hist, CST-ADJ &amp; WO-CLOSE after Oct 24'[trgl_gl_ref],&nbsp; &nbsp; 'Postline - Posting originreference'[PostingOriginReference]&nbsp;&nbsp;

johnt75 · Answer

Does the 'Postline query' table have a relationship, either direct or indirect, to&nbsp;'TR_Hist,&nbsp;CST-ADJ&nbsp;&amp;&nbsp;WO-CLOSE&nbsp;after&nbsp;Oct&nbsp;24'? What about the date table ?
Try running the below in DAX Query View
EVALUATE
CALCULATETABLE (
    'TR_Hist, CST-ADJ &amp; 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.

anonymous · Answer

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.

johnt75 · Answer

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] &gt;= DATE ( 2024, 10, 1 ),
                'Dates'[date] &lt; 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 &amp; 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 &amp; WO-CLOSE after Oct 24' )
)
ORDER BY
    'TR_Hist, CST-ADJ &amp; 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.

Forum Discussion

Tabels connection

6 Replies

Related content

Connection failed using a Folder connection in a pipeline

connection to dataflows

Power BI Oracle Connection Error – ORA-50201 (Failed to connect / parse connect string)

dataflow connection

Database connectivity.