topic USERELATIONSHIP with Filters in DAX Commands and Tips

USERELATIONSHIP with Filters

efstel — Fri, 13 Sep 2024 00:21:30 GMT

I currently am using the following with an active relationship between my Date Table and the OpportunitiesView. (The Date Table is connected to my SortMonth Table in order to keep the months in chronologic order.) I'm calculating the monthly running totals for each year.

ClosedWon running total in Month 2024 =

CALCULATE(

SUM(OpportunitiesView[ClosedWon]),

FILTER(

CALCULATETABLE(

SUMMARIZE('SortMonth', 'SortMonth'[Sort], 'SortMonth'[Month]),

ALLSELECTED('SortMonth')

ISONORAFTER(

'SortMonth'[Sort], MAX('SortMonth'[Sort]), DESC,

'SortMonth'[Month], MAX('SortMonth'[Month]), DESC

)

FILTER(

ALLSELECTED('Calendar'),

'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = 2024

)

I want to USERELATIONSHIP between ('Calendar'[Date], OpportunitiesView[CloseDate]),

I believe it needs to go after the SUM function and before the first FILTER function, but that isn’t working.

What am I doing wrong?

Thanks!

Re: USERELATIONSHIP with Filters

bhanu_gautam — Fri, 13 Sep 2024 03:02:36 GMT

efstel , Try using below DAX

ClosedWon running total in Month 2024 =
CALCULATE(
SUM(OpportunitiesView[ClosedWon]),
USERELATIONSHIP('Calendar'[Date], OpportunitiesView[CloseDate]),
FILTER(
CALCULATETABLE(
SUMMARIZE('SortMonth', 'SortMonth'[Sort], 'SortMonth'[Month]),
ALLSELECTED('SortMonth')
),
ISONORAFTER(
'SortMonth'[Sort], MAX('SortMonth'[Sort]), DESC,
'SortMonth'[Month], MAX('SortMonth'[Month]), DESC
)
),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = 2024
)
)

Re: USERELATIONSHIP with Filters

efstel — Fri, 13 Sep 2024 12:15:42 GMT

Thanks bhanu_gautam, but if you notice in my request above, that is exactly how I put it in as you have proposed. It is not working properly.

Re: USERELATIONSHIP with Filters

Anonymous — Mon, 16 Sep 2024 02:52:30 GMT

Hi efstel

Allow me to confirm a few questions for you:

1. Relationship Activation: Ensure that the relationship between Calendar[Date] and OpportunitiesView[CloseDate] is correctly defined and inactive by default.

2. Data Context: Verify that the data in OpportunitiesView and Calendar tables are correctly populated and that there are no missing or incorrect dates.

3. SortMonth Table: Confirm that the SortMonth table is correctly set up and that the Sort and Month columns are properly defined.

By adding USERELATIONSHIP('Calendar'[Date], OpportunitiesView[CloseDate]) within the CALCULATE function, it should correctly activate the relationship for your calculation.

After the above questions are confirmed, bhanu_gautam's reply should be effective.

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

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

Re: USERELATIONSHIP with Filters

efstel — Mon, 16 Sep 2024 15:09:29 GMT

I can confirm the above. It is working now. For whatever reason, the numbers were not matching up with my raw data. May have been user error. I may not have updated my dataset when I was checking them.