topic Re: [Basic] userelationship on variable table #2 in DAX Commands and Tips

[Basic] userelationship on variable table #2

ohnothimagain — Sat, 05 Oct 2024 12:29:54 GMT

Hello everyone : )
It's might be a basic but I've been stucking for weeks,hope anyone could tell me..

■trying to do

want to calculate schedule fluctuation count.
summarize a table for exculding duplicates,filter product name with a specific condition,

and count [PN] by time series like day,week etc

■Table/DAX/Issues

test11:=var _tbl= SUMMARIZE('TEST_','TEST_'[PN],'TEST_'[PO],"@M",min('TEST_'[M_Date]),"@R",min('TEST_'[R_Date])) var _ftd=filter(_tbl,[@M]<[@R]) var _result=CALCULATE(countrows(_ftd),USERELATIONSHIP('Calendar'[Date],'TEST_'[R_Date])) return _result

=> userelationship don't work.it brings a value of [M_date]

■attached file (I need to do it by powerpivot)
DAX summarize+userelationship

That's all.thank you for reading and continuing support!
Best regards,

Re: [Basic] userelationship on variable table #2

BeaBF — Fri, 04 Oct 2024 14:03:53 GMT

ohnothimagain Try with:

test11 :=
VAR _tbl =
SUMMARIZE(
'TEST_',
'TEST_'[PN],
'TEST_'[PO],
'TEST_'[M_Date],
'TEST_'[R_Date]
)
VAR _result =
CALCULATE(
COUNTROWS(_tbl),
'TEST_'[M_Date] < 'TEST_'[R_Date],
USERELATIONSHIP('Calendar'[Date], 'TEST_'[R_Date])
)
RETURN
_result

BBF

Re: [Basic] userelationship on variable table #2

ohnothimagain — Sat, 05 Oct 2024 05:50:25 GMT

Thank you for your quick responding!
I tried but unfortunately it was unsuccess(same result).

Re: [Basic] userelationship on variable table #2

Anonymous — Mon, 07 Oct 2024 07:51:43 GMT

Hi ohnothimagain ,

Based on the description, verify that there is an existing relationship between calendar[Date] and TEST[R_Date].

Then, try using the following DAX formula.

test11 := var _tbl = SUMMARIZE( 'TEST_', 'TEST_'[PN], 'TEST_'[PO], "@M", MIN('TEST_'[M_Date]), "@R", MIN('TEST_'[R_Date]) ) var _ftd = FILTER(_tbl, [@M] < [@R]) var _result = COUNTROWS( CALCULATETABLE( _ftd, USERELATIONSHIP('Calendar'[Date], 'TEST_'[R_Date]) ) ) return _result

You can also view the following document to learn more information.

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: [Basic] userelationship on variable table #2

ohnothimagain — Wed, 09 Oct 2024 14:36:24 GMT

Thank you for your reply and support...!

I tried,but....it was the same.relationship is exsiting between [date] and [R_date]

and If I don't use "summarize" ,relationship truely works.

anyone can give me more ideas?
(sorry for incovenient that I'm not available pbix file)