topic Re: How to CALCULATE VAR table with USERELATIONSHIP in DAX Commands and Tips

How to CALCULATE VAR table with USERELATIONSHIP

ohnothimagain — Fri, 16 Aug 2024 14:59:52 GMT

Hello everyone,
I have a table below,and want to sum [Qty] on variable table by cumulative and using inactive relationship
between [Calculated] and Calendar table.

Ftd_sum2:= VAR tbl = SELECTCOLUMNS( FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"), "@Trg", 'TEST_'[PN] ) VAR Dis =DISTINCT(tbl) VAR Ftd = CALCULATETABLE( 'TEST_',filter(allselected('TEST_'),'TEST_'[PN] IN Dis), USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated]) ) VAR Result = CALCULATE( SUM('TEST_'[Qty]), KEEPFILTERS(Ftd),filter(allselected('Calendar'), 'Calendar'[Date]<=max('Calendar'[Date])) ) return Result

Result is all blank..Upon checking the operarion,it caused when I added USERELATIONSHIP on CACULATETABLE.

This might be related to following article,I have no idea to do what I needed by using LOOKUPVALUE.
USERELATIONSHIP in calculated column
Could anyone teach me how to correct it on Excel powerpivot??

Best regards,

Re: How to CALCULATE VAR table with USERELATIONSHIP

jgeddes — Fri, 16 Aug 2024 15:36:34 GMT

The following measure might help you or at least get you pointed in the right direction.

Cumulative Sum = var _tbl = DISTINCT( SELECTCOLUMNS( FILTER(ALL(TEST_), TEST_[STS] = "〇"), "@Trg", TEST_[PN] ) ) var _result = CALCULATE( SUM(TEST_[Qty]), ALL('calendar'), FILTER(TEST_, TEST_[Calculated] <= MAX(calendar[Date]) && TEST_[PN] in _tbl), USERELATIONSHIP(TEST_[Calculated], calendar[Date]) ) RETURN _result

Re: How to CALCULATE VAR table with USERELATIONSHIP

ohnothimagain — Sun, 18 Aug 2024 11:14:49 GMT

Thank you for replying quickly! I tried right away,but result was unfortunately unsuccess.
-->putting userelationship on "calculated column" seems preventing

I've put this excel file on OneDrive,please teach me if you have more ideas.
OneDrive

Re: How to CALCULATE VAR table with USERELATIONSHIP

sjoerdvn — Mon, 19 Aug 2024 15:41:11 GMT

I don't know what's going on with all those filters and table calculations, but if you want to calculate a cummulative sum on that inactive date column, try:

Ftd_sum2:= VAR md = MAX('Calendar'[date]) RETURN CALCULATE( SUM('TEST_'[Qty]), USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated]), ALL('Calendar'), 'Calendar'[Date]<=md) )

If you need additional filters, apply them to the page or visual filters...

Re: How to CALCULATE VAR table with USERELATIONSHIP

ohnothimagain — Sat, 31 Aug 2024 04:47:52 GMT

I'm sorry for my late reply.Yes,your suggestion can success at most of cases.
the key point is it uses relationship between "calculated column" and calender.

>This might be related to following article,but I have no idea to do what I needed by using >LOOKUPVALUE.
>USERELATIONSHIP in calculated column