How to get column from many side of table using related function in ssas

[Zaheeralam]

Hi,

I am using ssas and creating reports on power bi.I get stuck and need help from you guys asap.

Below are the calcualted columns i am creating in ssas in 'Retail Application Table'
1) Salary_Multiple
 =IF('Retail_Applications'[Final_Income] <> 0, 
    ROUND('Retail_Applications'[Application_Amount]/ 'Retail_Applications'[Final_Income], 1), 
    99999999
)
2) Salary Buckets.
 =SWITCH( TRUE(),
AND('Retail_Applications'[PF_SalaryMultiple_1] >= 0, 'Retail_Applications'[PF_SalaryMultiple_1] <=5), "a [0 - 5]",
AND('Retail_Applications'[PF_SalaryMultiple_1] > 5, 'Retail_Applications'[PF_SalaryMultiple_1] <=10), "b (5 - 10]",
AND('Retail_Applications'[PF_SalaryMultiple_1] > 10, 'Retail_Applications'[PF_SalaryMultiple_1] <=15), "c (10 - 15]",
AND('Retail_Applications'[PF_SalaryMultiple_1] > 15, 'Retail_Applications'[PF_SalaryMultiple_1] <=20), "d (15 - 20]",
AND('Retail_Applications'[PF_SalaryMultiple_1] > 20, 'Retail_Applications'[PF_SalaryMultiple_1] <=25), "e (20 - 25]",
AND('Retail_Applications'[PF_SalaryMultiple_1] > 25, 'Retail_Applications'[PF_SalaryMultiple_1] <=1000 ), "f +25",
"Missing"
)

Now the issue is that in the salary_multiple column i want to use a column from another table ('Retail_portfolio'[orginal amount])
which is on many side of the relation ship ship.I tried using related but it's not working,can someone tell me how i can use the column from other table in my first calcuation and then i used that calcualted column in my next column.if this is not possible tell me if i don't that in measure a get same result.

Below is the actual caculation i nee.

Final ) Salary_Multiple
 =IF('Retail_Applications'[Final_Income] <> 0, 
    ROUND(Related('Retail_portfolio'[orginal amount])/ 'Retail_Applications'[Final_Income], 1), 
    99999999

I need your help to resolve this issue.

Thanks

[v-tianyich-msft]

Hi @Zaheeralam ,

Has your problem been solved? If not please share sample data to help you better.

Best regards,
Community Support Team_ Scott Chang

[shafiz_p · Accepted solution]

Hi @Zaheeralam ,

It sounds like you’re trying to use a column from the many side of a relationship in your calculated column. The RELATED function works when you want to bring in a value from the one side of a relationship, but it doesn’t work directly for the many side.

To achieve what you need, you can use the RELATEDTABLE function combined with an aggregation function.

Since don't know much about your relationship and data, you can try modifying your Salary_Multiple as below:

Salary_Multiple = IF(
    'Retail_Applications'[Final_Income] <> 0, 
    ROUND(
        CALCULATE(
            SUM('Retail_portfolio'[original amount]),
            RELATEDTABLE('Retail_portfolio')
        ) / 'Retail_Applications'[Final_Income], 
        1
    ), 
    99999999
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Zaheeralam]

Thank you, @shafiz_p , for your response. It seems like this will work, but I’ll update you after implementing it with the actual data

[Zaheeralam]

Can Someone help me for the ask question?

[Ritaf1983]

Hi @Zaheeralam 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Zaheeralam]

Hi @Ritaf1983 

Sure,let me try the solution given by @shafiz_p ,if it will not work then i wil share the sample data with you.

Thanks