Forum Discussion
Column based on another table
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.
Hello amtbew ,
try the following
Column = CALCULATE(COUNTROWS('Store Sales'),'Store Sales'[Sale Type] = "In Scope")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.
4 Replies
Hello amtbew ,
try the following
Column = CALCULATE(COUNTROWS('Store Sales'),'Store Sales'[Sale Type] = "In Scope")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.
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.
Thanks, Anonymous 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.
