Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

User Relationship function is not working

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, 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

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".

View solution in original post

1 REPLY 1
technolog
Super User
Super User

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".

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.