CODEWITHSUNDEEP
powerbirelationship
Siavoshkc
Siavoshkc

Reputation: 342

Filtering using an indirect relationship in Power BI

I have three tables with these relationships: 'T1'[B]<-'T2'[A] (Many to one respectively) & 'T2'[D]<-'T3'[A] (Many to one respectively)

Now imagine I want to have a report like this table: Items of 'T2'[C] | sum('T1'[C])

I also need to filter this table by selecting some items of T3[B]. What I did was creating a Measure: SumByT3 = CALCULATE(SUM('T1'[C]), USERELATIONSHIP('T1'[B], 'T2'[A]), USERELATIONSHIP('T2'[D],'T3'[A])) (While having these relationships defined.)

Then I put a page filter on T3[B]. After that I created a table with T2[C] as axis and SumByT3 as value. But it is not working. Even if I remove the page filter.

Upvotes: 0

Views: 1776

Answers (1)

smpa01
smpa01

Reputation: 4282

Try this measure which gives the SUM of all T1[C]

{that has the same filteredT2[A] as T1[B]}

{filteredT2 contains all T2[D] where T2[D]=T3[A]}

Measure =
CALCULATE (
    SUM ( 'T1'[C] ),
    TREATAS (
        SUMMARIZE (
            CALCULATETABLE ( T2, FILTER ( T2, ( T2[D] ) IN SUMMARIZE ( T3, T3[A] ) ) ),
            T2[A]
        ),
        T1[B]
    )
)

The following returns T2 that only has T2[D]=T3[A]

Table =
CALCULATETABLE ( T2, FILTER ( T2, ( T2[D] ) IN SUMMARIZE ( T3, T3[A] ) ) )

Then, only unique T2[A] are extracted with

SUMMARIZE (
            CALCULATETABLE ( T2, FILTER ( T2, ( T2[D] ) IN SUMMARIZE ( T3, T3[A] ) ) ),
            T2[A]
        )

which is used inside a TREATAS which creates an inner join of T2[A] and T1[B]

Upvotes: 0

Related Questions