Forum Discussion

ohnothimagain's avatar
ohnothimagain
Helper I
1 year ago

[Basic] userelationship on variable table #2

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,

4 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    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's avatar
      ohnothimagain
      Helper I
      1 year ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      ohnothimagain
      Helper I
      1 year ago

      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)