Create a measure using a relationship and then refer it back using a different relationship

[kk_shp_user]

I have a Fact_Sales table where there are - month_ending, id, name, sales

Every id is recruited by someone else. There is a different table for hierarchy dim_hierarchy - id, recruiter_id, recruitment_date

I want to have measures for,

1. how many new recruits someone had in a particular month

2. how much sales did a particular recruiter get from all their new joiners

If I join Fact_sales[id] to dim_hierarchy[recruiter_id], it becomes a many-to-many relation. So I created a bridge table with distinct ids and used that to connect the two tables. 

I am able to get the first measure -  I can do this with or without a relationship using TREATAS. However I am stuck with creating the second measure, where I need to get the sales of all the new recruits for a Fact_sales[id]. The reason being, I need some way to calculate the sales for all the ids in dim_hierarchy which I have to pull from Fact_Sales and then refer this back in the measure I am trying to create.

[v-sgandrathi]

HI @kk_shp_user,

Thankyou  @Elena_Kalina and @danextian for your reply on the issue.

Has your issue been resolved? If the community member's response answered your query, please confirm it! Marking a reply as a solution helps others with similar questions and keeps the community helpful and organized. If the response was helpful, giving Kudos is always appreciated!

[danextian]

Hi @kk_shp_user 

It would be easier for us to provide a more suitable solution had you provided a workable sample (not an image ) data and your expected result from that.  You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[Elena_Kalina · Accepted solution]

Hi @kk_shp_user 

Please, try these measures

Sales From Recruits = 
VAR CurrentRecruiterID = SELECTEDVALUE(Fact_Sales[id])
VAR RecruitsOfCurrentRecruiter = 
    CALCULATETABLE(
        VALUES(dim_hierarchy[id]),
        FILTER(
            ALL(dim_hierarchy),
            dim_hierarchy[recruiter_id] = CurrentRecruiterID
        )
    )
VAR SalesFromRecruits =
    CALCULATE(
        SUM(Fact_Sales[sales]),
        Fact_Sales[id] IN RecruitsOfCurrentRecruiter
    )
RETURN
    SalesFromRecruitsFor Measure #1 (Count of New Recruits)

New Recruits Count = 
VAR CurrentMonth = SELECTEDVALUE(Fact_Sales[month_ending])
VAR CurrentRecruiterID = SELECTEDVALUE(Fact_Sales[id])
RETURN
    CALCULATE(
        COUNTROWS(dim_hierarchy),
        FILTER(
            ALL(dim_hierarchy),
            dim_hierarchy[recruiter_id] = CurrentRecruiterID &&
            EOMONTH(dim_hierarchy[recruitment_date], 0) = CurrentMonth
        )
    )