CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
M J
M J

Reputation: 33

How can I reference another table when creating a measure based on one table in DAX PowerBI?

I have one table (Residency) that looks like the following:

Person Resident
1852 1
1863 1
1963 0

I'm currently calculating the percent who are residents using the following:

Per Housing First Year = DIVIDE(CALCULATE(COUNT(Sheet1[First_Fall_Term_Res]), Sheet1[First_Fall_Term_Res] = 1), COUNT(Sheet1[First_Fall_Term_Res]))

I have another table (Athletes) like the following:

ID Athlete
1852 Yes
1863 No
1963 Yes

I have these tables joined on Person and ID. I would like to calculate the Per Housing First Year who are Athletes. How can I build out a formula that allows me to filter on the Athlete column in another table? I've used CALCUALTE to set filter conditions within the same table. For example if the Athlete column was on the same table I could do the following:

Per Housing First Year = DIVIDE(CALCULATE(COUNT(Sheet1[First_Fall_Term_Res]), Sheet1[First_Fall_Term_Res] = 1, Sheet1[Athlete] = "Yes"), COUNT(Sheet1[First_Fall_Term_Res]))

But how can I reference another table?

Upvotes: 0

Views: 307

Answers (1)

Nick A
Nick A

Reputation: 1124

Since both tables are connected by a relationship, you can use the RELATED() function to reference the Athletes table. Here is the documentation for this function.

Your new measure would look like this:

PerHousingFirstYear_Athletes = 
DIVIDE(
    CALCULATE(
        COUNT(Residency[Resident]),
        Residency[Resident] = 1,
        RELATED(Athletes[Athlete]) = "Yes"
    ),
    COUNT(Residency[Resident])
)

Upvotes: 0

Related Questions