User Relationship function is not working

Anonymous — Fri, 04 Oct 2019 17:41:42 GMT

Hi,

I have to findout localdate from the date table based on the timezone matched with the fact table. I am using below dax but its not working.

CurrentDay = CALCULATE (VALUES(CalendarDate[LocalDateTime]),

USERELATIONSHIP(Sales[TimeZone],CalendarDate[TimeZone] ))

I have the calendarDate table having multiple sets of dates for each timezone and Current LocalDateTime calculated based on the timezone

In fact table i have getting amount value with the timezone and localtime for the timezone for that country.

I have to calculate Last 12 months Flag in Calendar Date table based on the Country selected from the Sales table in the report filter.

I have added active relationship on CalendarDate - "Date" column with Sales - "Date" column

and inactive relationship on "TimeZone" column

Below is the table schema :

CalendarDate Sales

Date TimeZone LocalTime Date TimeZone LocalTime Country

1/1/2019 GMT 4/10/2019 1/10/2019 GMT 4/10/2019 UK

-------- IST 5/10/2019 ------- IST 5/10/2019 INDIA

----

Thanks,

Re: User Relationship function is not working

technolog — Sat, 08 Jun 2024 01:58:49 GMT

To achieve the required calculations using the inactive relationship in DAX, you need to activate the relationship within the calculation. Here’s how you can properly set up the calculation for the CurrentDay and subsequently use it to flag the last 12 months based on the country selected.

Step 1: Correct Calculation of CurrentDay
The DAX expression you provided should work with USERELATIONSHIP, but you need to ensure it’s properly structured. Also, since the VALUES function returns a table, you might want to use SELECTEDVALUE or MIN/MAX if you expect a single value.

CurrentDay =
CALCULATE(
SELECTEDVALUE(CalendarDate[LocalDateTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
Step 2: Flagging Last 12 Months
Next, you need to create the flag for the last 12 months in the CalendarDate table based on the country selected. Ensure that you are correctly using the CurrentDay calculated column.

Adjusting the Date Column for the Last 12 Months
You’ll need a calculated column or measure to determine if the date falls within the last 12 months from the CurrentDay.

Calculated Column for Last 12 Months Flag
First, ensure your CurrentDay calculation works and reflects the correct local date. Then, use this value to create a flag for the last 12 months.

Step 3: Create the Last 12 Months Flag in CalendarDate
Create a calculated column in the CalendarDate table to flag dates within the last 12 months based on the local date:

Last12MonthsFlag =
VAR CurrentLocalDate =
CALCULATE(
MAX(Sales[LocalTime]),
USERELATIONSHIP(Sales[TimeZone], CalendarDate[TimeZone])
)
RETURN
IF(
CalendarDate[LocalDateTime] >= EDATE(CurrentLocalDate, -12) && CalendarDate[LocalDateTime] <= CurrentLocalDate,
"Last 12 Months",
"Not Last 12 Months"
)
Step 4: Ensure Proper Relationships
Make sure your data model has the following relationships set correctly:

Active relationship between CalendarDate[Date] and Sales[Date].
Inactive relationship between CalendarDate[TimeZone] and Sales[TimeZone].
Step 5: Use the Flag in Reports
Use the Last12MonthsFlag in your visual filters to show or hide data based on the flag:

Add the Last12MonthsFlag to your visuals as a filter.
Set the filter to display only data where Last12MonthsFlag equals "Last 12 Months".

topic User Relationship function is not working in DAX Commands and Tips

User Relationship function is not working

Re: User Relationship function is not working