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

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,091 guests
Please welcome our newest community members: