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

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        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.