cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarryS Frequent Visitor
Frequent Visitor

use relationship and filtering

Hi everyone, am new to powerbi and these forums have been a great help to me so far. However, I have found myself stuck on a particular issue when using the ‘userelationship’ and  'filter' function. Unfortunately due to the data am working with am not able to share a picture but I will show you the formula am using (just with some of the words changed).

 

So I need to use the ‘userelationship’ function for a specific calculation and when I try to use the 'filter' function it does not bring back the correct figures. Here are the formulas I have been using.  Also, I need to work in direct query mode if that is important, thanks.

 

Use relationship measure = CALCULATE( [numbers], USERELATIONSHIP(Date_Reference[Date], 'POWER_BI table' [otherdate]))

 

Filtering = CALCULATE([Use relationship measure], FILTER(Date_Reference, Date_Reference[Fiscal_Year] = 201819), FILTER(code, code = "000000"))  * note - the speech marks round the code number are because this code although a number needs to be read as a word *

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
HarryS Frequent Visitor
Frequent Visitor

Re: use relationship and filtering

HI Jimmy, I manged to reslove this issue by using it as a 'variable'. Also I had my relationship both ways not single which was the causing an issue as well. 

 

Thanks,

Harry

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: use relationship and filtering

@HarryS ,

 

The issue may be caused by direction in table relationship. If 'Date_Reference' and 'code' are two dimentional tables, I would suggest you to use CROSSFILTER() and set the filter direction instead of two 'FILTER' statement.

 

Community Support Team _ Jimmy Tao

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

Community Support Team
Community Support Team

Re: use relationship and filtering

@HarryS ,

 

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao

Highlighted
HarryS Frequent Visitor
Frequent Visitor

Re: use relationship and filtering

HI Jimmy, I manged to reslove this issue by using it as a 'variable'. Also I had my relationship both ways not single which was the causing an issue as well. 

 

Thanks,

Harry

View solution in original post

raj_sub_1994 Frequent Visitor
Frequent Visitor

Re: use relationship and filtering

Is it possible for you to give the code or the syntax of the same since I cannot get your reference of "it"

raj_sub_1994 Frequent Visitor
Frequent Visitor

Re: use relationship and filtering

Is it possible for you to give the code or the syntax of the same since I cannot get your reference of "it"

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors