Forum Discussion
How to get column from many side of table using related function in ssas
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
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
6 Replies
Can Someone help me for the ask question?
- Ritaf1983
Super User
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.
- shafiz_p
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 HafizThank you, shafiz_p , for your response. It seems like this will work, but I’ll update you after implementing it with the actual data
Hi Zaheeralam ,
Has your problem been solved? If not please share sample data to help you better.
Best regards,
Community Support Team_ Scott Chang
