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
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.