topic Re: USERELATIONSHIP THROWING ERROR in DAX Commands and Tips

USERELATIONSHIP THROWING ERROR

Anonymous — Fri, 06 Jan 2023 04:41:37 GMT

Hello,

Below code throws an error "USERELATIONSHIP function can only use the two columns references participating in relationship."

Cases Last 13 Month =

var MaxDate = MAX('Calendar'[Calendar_last_day_of_month])

var MaxDate_13MonthsAgo = DATESINPERIOD('Presenatation calendar'[Calendar_last_day_of_month],MaxDate, -13,MONTH)

var Results = CALCULATE(Actuals_by_Month[Actuals_Volume],

REMOVEFILTERS('Calendar') ,

KEEPFILTERS(MaxDate_13MonthsAgo),

USERELATIONSHIP('Calendar'[Calendar_last_day_of_month],'Presenatation calendar'[Calendar_last_day_of_month]))

RETURN

Results

Notes: I already have inactive relationship established between Calendare table and presentation table
Could someone can tell whats wrong here?

Thanks

Re: USERELATIONSHIP THROWING ERROR

Mahesh0016 — Fri, 06 Jan 2023 05:18:01 GMT

Anonymous please Try this formula.

Cases Last 13 Month =

var MaxDate = CALCULATE(

MAX('Calendar'[Calendar_last_day_of_month])

,USERELATIONSHIP('Calendar'[Calendar_last_day_of_month]

,'Presenatation calendar'[Calendar_last_day_of_month])

)

var MaxDate_13MonthsAgo =

DATESINPERIOD(

'Presenatation calendar'[Calendar_last_day_of_month]

,MaxDate

, -13

,MONTH

)

var Results =

CALCULATE(

Actuals_by_Month[Actuals_Volume],
REMOVEFILTERS('Calendar') ,
KEEPFILTERS(MaxDate_13MonthsAgo)
)

RETURN
Results

** If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

Re: USERELATIONSHIP THROWING ERROR

Anonymous — Sat, 07 Jan 2023 02:08:57 GMT

Thank you Mahesh0016 , BUT GETTING SAME ERROR.

Re: USERELATIONSHIP THROWING ERROR

Mahesh0016 — Mon, 09 Jan 2023 04:15:59 GMT

Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: USERELATIONSHIP THROWING ERROR

Anonymous — Mon, 09 Jan 2023 22:37:25 GMT

Hi Mahesh0016 ,

I wish I could share the pbix file after removing the sensitive data but the file is bit of large and removing things could take a lot of time.

. however my goal is to user should be able to see past 13 months of data based on the month selected. for example. if some one selects "01/31/2023" , user should see past 13 months iof data like below

Date Volume
01/31/2023 10
12/31/2022 30
.
.
.
01/31/2022 35

my input table is also in the same format as above but with one additional column added

Date site Volume
01/31/2023 1 5

01/31/2023 2 5
12/31/2022 1 10

12/31/2022 2 10

12/31/2022 3 10
.
.
.
01/31/2022 3 35

I am generating calendar like this

Calendar = CALENDARAUTO()

and presentation calendar = 'Calendar'.

Please let me know if these info help. Thanks