Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Olenard
Helper III
Helper III

Inactiv Relationship in Calculated column

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 !

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

AlB
Super User
Super User

@Anonymous 

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

image.png

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 

SU18_powerbi_badge

 

 

   

 

 

View solution in original post

9 REPLIES 9
AlB
Super User
Super User

@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 

SU18_powerbi_badge

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

@Anonymous 

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

image.png

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 

SU18_powerbi_badge

 

 

   

 

 

Anonymous
Not applicable

@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
Not applicable

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

@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

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

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 

SU18_powerbi_badge

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors