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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
LeoST
Frequent Visitor

Switching between two relationships between two tables

Hello,

 

I've two tables that are conneted by two relations. One table is costs and the other is Projects.

They have an active relation based on there ID columns and an inactive relation between Costs[Source ID] abd Projects[ID].

LeoST_0-1605775695836.png

Now I want to create a table in Power BI showing me: Project Name, Cost and Name of the Source project. The first two columns are no problem at all, but the last one doesn't want to work. I tried deactivating the active relation for that using Crossfilter function and activating the connection between Costs[Source ID] and Projects[ID] using USERELATIONSHIP but my table either is empty or shows wrong data all the time.

My measure:

Measure =
CALCULATE(
FIRSTNONBLANK(Projects[Name],TRUE()),
USERELATIONSHIP(Costs[Source ID, Projects[ID]), CROSSFILTER(Costs[ID],Projects[ID],none))
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@LeoST , Try like

Measure =
CALCULATE(
FIRSTNONBLANK(Projects[Name],TRUE()),
USERELATIONSHIP(Costs[Source ID], Projects[ID]))

 

Not need crossfilter

refeR: https://youtu.be/e6Y-l_JtCq4

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

If you want to have a model where there is a(n) (inactive) relationship between Costs[SourceID] and Projects[ID] (but stick to one-way filtering, please) and you want to use this relationship insted of TREATAS (virtual relationship), then your measure would be this:

Source Project v2 = 
var __shouldCalculate = true()
    && HASONEVALUE( Projects[ID] )
    && not ISEMPTY( Costs )
var __output =
    IF( __shouldCalculate,
        CALCULATE(
            SELECTEDVALUE(
                Projects[Name],
                "Many Sources"
            ),
            DISTINCT( Costs[SourceID] ),
            ALL( Costs ),
            USERELATIONSHIP(
                Costs[SourceID],
                Projects[ID]
            ),
            CROSSFILTER(
                Costs[SourceID],
                Projects[ID],
                Both
            )
        )
    )
RETURN
    __output

 

Anonymous
Not applicable

 

 

// In Costs, columns ID, Cost and Source ID
// should be hidden (yes, that's right).
// In Projects only Name can be visible.
// You join Projects[ID] to Costs[ID] using
// the standard one-way relationship.
// You join Projects[ID] to Costs[Source ID] using
// the standard one-way relationship (for these
// measures, though, this relationship is not required, so
// you can remove this relationship completely).
// The latter relationship will be inactive, if created.
// Once this CORRECT setup is in place, you define
// 2 measures below.

[Cost] = SUM( Costs[Cost] )

[Source Project] =
var __shouldCalculate = true()
    && HASONEVALUE( Projects[ID] )
    && not ISEMPTY( Costs )
var __output =
    IF( __shouldCalculate,
        CALCULATE(
            SELECTEDVALUE(
                Projects[Name],
                "Many Sources"
            ),
            TREATAS(
                DISTINCT( Costs[Source ID] ),
                Projects[ID]
            ),
            ALL( Costs )
        )
    )
RETURN
    __output

 

 

LeoST
Frequent Visitor

Hey,

I also tried that but it gives me this result (first row the measure should return "Project D"):

LeoST_0-1605778023399.png

LeoST_1-1605778051543.png

 

 

CNENFRNL
Community Champion
Community Champion

Hi, @LeoST , you might want to try this measure

Measure = 
VAR __id = MAX ( Costs[Source ID] )
VAR __name = LOOKUPVALUE ( Projects[Name], Projects[ID], __id )
RETURN
    __name

Screenshot 2020-11-19 170130.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Hi @CNENFRNL 

 

LOOKUPVALUE is a very slow function and should be use only when there's absolutely no other way to get what you want (there are situations like this but they are far and between). It's much better to use TREATAS in this case with a suitable expression. On top of that, there should be no value for the Total row if there is more than 1 row selected since "Project D" makes no sense in the above screenshot.

amitchandak
Super User
Super User

@LeoST , Try like

Measure =
CALCULATE(
FIRSTNONBLANK(Projects[Name],TRUE()),
USERELATIONSHIP(Costs[Source ID], Projects[ID]))

 

Not need crossfilter

refeR: https://youtu.be/e6Y-l_JtCq4

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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