Logic to calculate measure when two tables do not have direct relationship.

[damit23183]

Hi, 

I have two tables as seen below,

and 

I want to calculate Relative Risk based on this formula;

RR = IE(CE+CN)/CE(IE+IN)

IE = Total ID where Race = African or Asian or White or UnKnown From Table A

CE = Total ID where Race = White from Table B

IN = Total ID where Race = African or Asian or White or UnKnown From Table B

CN = Total ID where Race = White from Table B

(Total ID from Table B should not exists in Table A), therefore based on given value above RR formula should be like this,

Final Result I would like to see;

RR =  1(3+3)/3(1+1). 

NOTE : There is no relatinship between these tables except some ID.

I want to create dynamic logic where other categories like Gender, Ethnicity, Race, Agegroup should be calculate.

Also, here CE and CN are reference number from each categories. i.e in current example I have used WHITE as Race categories , but gender it will be M, for Age group it will 50-60.

If you have questions please let me know happy to answer everything.

Thanks

[damit23183]

Thank you guys for your response. I tried both the solutions you have provided but could not get expected answer. Therefore, i decieded to do everything in SQL and just pull measure from table.

Everything working fine from SQL as expected. But, thanks for your suggestion as I came to know something new.

Thanks

[Anonymous]

Hi, @damit23183 

I am happy to help you, but according to your description there are a few points of uncertainty you need to answer, which is important to solve your problem.

1. Why are the IE and IN values 1, and how did you arrive at this result?
2. Are you sure Table B id value is not a consecutive number but the table shows 1, 2, 6, 7, 8?

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@damit23183 , Append two tables after adding a flag for Table 1 and Table 2 and then create these measures

Second create common dimensions like Race , gender and ID, and then you can analyse them together with common dim

Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...