Multiple relationships between tables in same expression

[Anonymous]

Hi! 

I'm stuck and would very much appreciate some help 🙂 

I have two tables that lookes like this, with two different relations: 

I have Measure 1: Number of rows = DISTINCTCOUNT('FactTable'[ID])

and then I would like to have another Measure 2:
Number of rows, Other department = 

CALCULATE([Number of rows];FILTER(FactTable;

"Physical team's department" <> "Responsible team's department")

But I don't know how to write this.... 

I have come so far that I think that the first part of the filter-expression should be: RELATED(DimOrganizationTable[Department]) <>..... But the second part I can't figure out. I'm thinking that it's maybe somthing with USERELATIONSHIP. But I can't get it to work. 

Thanks so much in advance! And have a nice weekend! 🙂

[vivran22 · Accepted solution]

Hello @Anonymous 

You may try this:

Other department =
CALCULATE (
    [Number of rows],
    FILTER (
        FactTable,
        RELATED ( dimOrgTable[Department] )
            <> LOOKUPVALUE (
                dimOrgTable[Department],
                dimOrgTable[Team_PK], FactTable[Responsible Team]
            )
    )
)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may create a measure as follows.

Count = 
CALCULATE(
    DISTINCTCOUNT(FactTable[ID]),
    FILTER(
        FactTable,
        LOOKUPVALUE(DimOrangnizationTable[Department],DimOrangnizationTable[Team_PK],FactTable[Physical team]) <> 
        LOOKUPVALUE(DimOrangnizationTable[Department],DimOrangnizationTable[Team_PK],FactTable[Responsible team])
    )
)

Resutl:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Here's my take.  In the Fact Table, create a calculated column to bring over the Physical Teams's department column from the Dim Table

Column1 = RELATED(DimOrganizationTable[Department])

Column2 = LOOKUPVALUE(DimOrganizationTable[Department],DimOrganizationTable[Team_PK],FactTable[ID])

Now write this measure

=CALCULATE([Number of rows],FILTER(FactTable,[Column1]<>[Column2]))

Hope this helps.

[vivran22 · Accepted solution]

Hello @Anonymous 

You may try this:

Other department =
CALCULATE (
    [Number of rows],
    FILTER (
        FactTable,
        RELATED ( dimOrgTable[Department] )
            <> LOOKUPVALUE (
                dimOrgTable[Department],
                dimOrgTable[Team_PK], FactTable[Responsible Team]
            )
    )
)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

Thank you so much! 🙂