use relationship and filtering

[HarryS]

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 *

[v-yuta-msft]

@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 · Accepted solution]

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

[id013]

Hi @HarryS 

I'm having almost the same exact problem, could you describe more in detail what you did to solve it? 

Thanks in advance

[Anonymous]

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

[Anonymous]

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

[v-yuta-msft]

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