Use relationship with filters

[Analitika]

Have to use filter on userelationship?

Measure 2 =

CALCULATE(
sum('Table1'[DebtSumNacCurrency]),
'Table1'[OPERACNO]='Table2'[SkOperacNo],
USERELATIONSHIP('Table2'[SkOperacNo],'Table1'[OPERACNO])
)

got error on ='Table2'[SkOperacNo]  - The expression contains multiple columns

trying to add max('Table2'[SkOperacNo]) but error - A function Max has been used in a True/False expression

[amitchandak]

@Analitika , You do nor need them when you use use reltion

CALCULATE(
sum('Table1'[DebtSumNacCurrency]),
USERELATIONSHIP('Table2'[SkOperacNo],'Table1'[OPERACNO])
)

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Analitika]

It duplicates values wrong

but must return only one value where SkOperacNo = 64477 

[tex628]

You need to remove OPERACNO from the table. The relationship is only active in the measure calculation, you cant display both columns in the table as the relationship will be inactive there. 

/ J

[Analitika]

I tryed, not make any sence, result is same

[tex628]

You cant have any columns from table1. Remove all columns from table 1, if there's still duplicates of 64477 after that the issue is related to something else. 

[Analitika]

But i need to have them

[tex628]

The USERELATIONSHIP() only works within the specific measure. For all the other columns there is no relationship between table 1 and table 2 which is why you can not combine columns from the 2 different tables! 

If you can provide an image of all the data you need displayed in your table I can help you accomplish this, but untill then it's very hard to help. 

/ J

[Analitika]

I want get everything from Table2 and only one value from 'Table1'[DebtSumNacCurrency]

[AllisonKennedy]

@Analitika
You are creating a MEASURE, so the measure does not have the row context of Table2. Only when you put the measure in a visual will it calculated according to what is in the table visual or matrix or column chart.

Could you please share a screenshot of your Model view so we can see the relationships. From the screenshot you have shared already, it looks like the measure is not using the relationship properly. Does 64477 exist in both tables as Table2'[SkOperacNo] and Table1'[OPERACNO] ?

[Analitika]

PBIX 

DOKMNR must be only from Table1

ApNr must be  from Table2 where OPERACNO = DengOperacNo

[AntrikshSharma]

@Analitika  You should share a sample file now, also which one of the two tables is on the many side?

Measure 2 =
CALCULATE (
    SUM ( 'Table1'[DebtSumNacCurrency] ),
    ALLEXCEPT ( 'Table1', 'Table2'[SkOperacNo] ),
    USERELATIONSHIP ( 'Table2'[SkOperacNo], 'Table1'[OPERACNO] )
)

[Analitika]

Table2 on many side, your example is not work as i cant set 

ALLEXCEPT ( 'Table1',) 'Table2'[SkOperacNo]  

[amitchandak]

@Analitika , saw your update.

Can you share sample data and sample output in table format?

[tex628]

You should be able to remove the filterstatement from the measure :

Measure 2 = 
CALCULATE(
sum('Table1'[DebtSumNacCurrency]),
USERELATIONSHIP('Table2'[SkOperacNo],'Table1'[OPERACNO])
)

It has the same function as the userelationship. 

I can't really see anything about this measure that would cause an error. Are you trying to add something to the measure? Can you post the exact error message? 

/ J

/ r

[Analitika]

It give me a wrong total, it sums all table sums but not needed