Many to many related column.

[Anonymous]

Hello,

I have 3 tables, two of them contains data and the another one is a bridge table to avoid many to many relationship error. Like this:

Workers                Bridge                  Goals

worker_id   *---1   worker_id  1--- *  worker_id

job_title                                            goal_value

I need to create a measure on Goals table with the following bussiness logic, if a worker's job title is equal tos "GP" than goal_value is equal to the sum of goal_value plus 10, else goal_value is equal to sum of goal value. 

Translating two dax, I need something like this on Goals table:

my_measure = 

    VAR
        job_title = SOME_CODE_OR_FUNCTION(Workers[job_title])

   VAR

        _sum = SUM(goal_value)

    RETURN

        IF(job_title = "GP"; _sum + 10; _sum)

But I don't known how to obtain a worker's job title from my Goals table measure. How can I do that?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try this measure:

my_measure =
IF (
    LASTNONBLANK ( Workers[job_title], 1 ) = "GP",
    SUM ( Goals[goal_value] ) + 10,
    SUM ( Goals[goal_value] )
)

Insert a table visual, add 'Bridge'[worker_id], 'Workers'[job_title] and [my_measure] into it.

Best regards,

Yuliana Gu

[RolandsP]

How about this?

my_measure = 

   VAR

        _sum = SUM(goal_value)

    RETURN

        IF((Workers[job_title] = "GP"; _sum + 10; _sum)

[Anonymous]

I can not do this, since I am doing this measure on Goals table.

[RolandsP]

OK, I got. Then try this one

my_measure = 

    VAR
        job_title = SELECTEDVALUE(Workers[job_title])

   VAR

        _sum = SUM(goal_value)

    RETURN

        IF(job_title = "GP"; _sum + 10; _sum)