Forum Discussion

LeoST's avatar
LeoST
Frequent Visitor
5 years ago
Solved

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

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

6 Replies

  • LeoST's avatar
    LeoST
    Frequent Visitor
    5 years ago

    Hey,

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

     

     

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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