cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,535 guests
Please welcome our newest community members: