Column based on another table

[amtbew]

I have the two following tables, connected by Store ID.

Store Proposals

Store ID	Proposal
A1	$      500.00
A2	$      700.00
A3	$      350.00

Store Sales

Store ID	Sale ID	Sale Type	Sale Value
A1	100	In Scope	$           125.00
A1	101	Out of Scope	$           300.00
A2	102	Out of Scope	$           125.00
A3	103	In Scope	$           275.00
A3	104	Out of Scope	$           250.00

Looking to add a column to Store Proposals table that indicates whether or not the Store ID has any In Scope sales.

[Idrissshatila · Accepted solution]

Hello @amtbew ,

try the following

Column = CALCULATE(COUNTROWS('Store Sales'),'Store Sales'[Sale Type] = "In Scope")

[v-kaiyue-msft · Accepted solution]

Hi @amtbew ,

If you want to put the result in the if function for judgment, just change the expression to:

InScopeSalesExists =
IF(
CALCULATE(
COUNTROWS('Store Sales'),
'Store Sales'[Sale Type] = "In Scope"
) > 0,
"Yes",
"No"
)

The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amtbew]

Thanks, @v-kaiyue-msft this works but I think I need a different approach. I don't think I included enough info about my situation so I'll mark this as solved and open a fresh request with a better description. 

[Idrissshatila · Accepted solution]

Hello @amtbew ,

try the following

Column = CALCULATE(COUNTROWS('Store Sales'),'Store Sales'[Sale Type] = "In Scope")

[amtbew]

Thanks @Idrisshalita 

Any alternative to using Countrows? I tried to wrap it in an If statement to produce a true or false but received a circular reference. Hoping to use this column a True/false slicer.