[Basic] userelationship on variable table #2

[ohnothimagain]

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,

[Anonymous]

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.

[ohnothimagain]

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)

[BeaBF]

@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

[ohnothimagain]

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