Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Tabels connection

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...
Connection
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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