topic Re: Tabels connection in DAX Commands and Tips

Tabels connection

Anonymous — Tue, 17 Dec 2024 13:27:16 GMT

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 🙂

Re: Tabels connection

johnt75 — Tue, 17 Dec 2024 14:45:35 GMT

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.

Re: Tabels connection

Anonymous — Tue, 17 Dec 2024 15:10:36 GMT

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],

'Postline - Posting originreference'[PostingOriginReference],

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

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

'Postline - Posting originreference'[PostingOriginReference]

Re: Tabels connection

johnt75 — Tue, 17 Dec 2024 15:23:04 GMT

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.

Re: Tabels connection

Anonymous — Tue, 17 Dec 2024 15:51:04 GMT

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

Re: Tabels connection

johnt75 — Tue, 17 Dec 2024 16:27:00 GMT

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.

Re: Tabels connection

Anonymous — Wed, 18 Dec 2024 03:11:22 GMT

Hi Anonymous ,

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.