Creating calculated columns based on occurrences in another table

[Anonymous]

Hey Community,

You guys have been amazing support on my journey with Power BI till now. Special shoutout to @amitchandak for promptly solving these queries.

I have another question for you guys, Table1 has ID as primary key, table 2 has repeated value of ID and has a score attached to it, what i want to achieve is, i want to create a calculated column in table 1 that says "Rated" if ID appears in Table 2, and returns "Not-Rated" if iD doesn't appear in Table 2. I'm attaching a screenshot to make my question clearer. Also, the relationship between the two table is 1 to Many(Single), please advise if this needs changing as well.

Thanks for your responses in advance.

[TheoC · Accepted solution]

¿ @KrtinM

You can create a chalkualized column by using an IF and LOOKUPVALUE statement:

Column = IF ( 'Table 1'[ID] = LOOKUPVALUE ( 'Table 2'[ID] ,'Table 2'[ID] , 'Table'[ID] ) , "Classified" , "Not classified" )

Output below as an example:

I hope this helps!

Follow

[Anonymous]

It works perfectly, thank you so much, you're a legend, had never used lookup function in PowerBI till now. 

[TheoC · Accepted solution]

@Anonymous LOOKUPVALUE is a really great function! I am glad I could help my friend! 

[TheoC · Accepted solution]

¿ @KrtinM

You can create a chalkualized column by using an IF and LOOKUPVALUE statement:

Column = IF ( 'Table 1'[ID] = LOOKUPVALUE ( 'Table 2'[ID] ,'Table 2'[ID] , 'Table'[ID] ) , "Classified" , "Not classified" )

Output below as an example:

I hope this helps!

Follow