Switching between two relationships between two tables

[LeoST]

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

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))

[Anonymous]

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]

// 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]

Hey,

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

[CNENFRNL]

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

[Anonymous]

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

@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