Inactiv Relationship in Calculated column

[Olenard]

Dear Power BI Community ! 

I am trying to feed a calculated column using an inactive relationship.

The idea is have an equivalent of RELATED  between two tables that have inactiv relationship to feed a calculated column.

Any idea of how to do it ?

best !

[amitchandak · Accepted solution]

@Olenard , In the case of a column related, will not work with inactive relation, and use relation might not work well.

You can use lookupvalue or can use a formula like this

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

refer to this video : https://www.youtube.com/watch?v=czNHt7UXIe8

[nhch]

The right way to solve this is to enable Two-Ways filtering in the inactive relationship and use the USERELATIONSHIP in the FILTER.
All the other answers are ugly workarounds.

[AlB]

@Olenard 

If you show the details your code (that iterative (line by line) formula) and/or provide more info on the model, ideally sharing the pbix, perhaps we can find a less resource-heavy solution.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Olenard]

Hi ! 

I used a variable that provided a 'true'/'flase'. This is working fine with calculate then.

Next step to be able to 'store' this value and return the value in the return statement.

var _checkID=CALCULATE(FIRSTNONBLANK(Adobe_Consumers[People Id];Adobe_Consumers[People Id]);USERELATIONSHIP(Adobe_Consumers[People Id];Infoware_Consumers[people id]))

RETURN

ISBLANK(_checkID)||ISERROR(_checkID)

[AlB · Accepted solution]

@Anonymous 

Of course you can change the active relationship when doing calculations in a table. For instance, if you have a simple model like:

with the relationship inactive, you can create the calculated column:

Res = CALCULATE(SUM(Table1[Col1]), USERELATIONSHIP(DimT[Col1],Table1[Col1]))

on table DimT, which will work fine using the otherwise inactive relationship.

It is true that it won't work with RELATED( ) as I suggested earlier but that has nothing to do with USERELATIONSHIP( ) but rather with CALCULATE( ). RELATED( ) requires a row context and that will be lost through context transition when using CALCULATE( ). A calc column in Table1 such as:

 New col =  CALCULATE( RELATED(DimT[Col2]) )

with the an active relationship will not work either, precisely for that reason, while this would:

 New col =  RELATED( DimT[Col2] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

"Of course you can change the active relationship when doing calculations in a table". Of course, you can't, @AlB. We're talking here about RELATED in fact and USERELATIONSHIP can't change the connection in a table (calculated column) in a way that RELATED work how one would expect it; hence you have to use LOOKUPVALUE. There's an article about it on www.sqlbi.com and in their book as well , as I mentioned. Here's something you SHOULD read: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

[Anonymous]

Sorry but you can't change the active relationship when doing calculations in a table. USERELATIONSHIP will have no effect. Your best function to do what you want is LOOKUPVALUE. It's not only the best one. IT'S THE ONLY ONE. You'll find the explanation of this behaviour in one of the articles on www.sqlbi.com.

[amitchandak · Accepted solution]

@Olenard , In the case of a column related, will not work with inactive relation, and use relation might not work well.

You can use lookupvalue or can use a formula like this

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

refer to this video : https://www.youtube.com/watch?v=czNHt7UXIe8

[Olenard]

Great one ! The only thing is that this is an iterative (line by line) formula. Then quite resource consuming!

But if there is no solution with an inactive relationship (seems like it does not work with the calculated column), this is the solution

[AlB]

Hi @Olenard 

Just use USERELATIONSHIP to activate the relationship and then RELATED as usual

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Olenard]

Hi,
Do you have an example. Because to use USERELATIONSHIP you need to use caculate. Not made to display a text value