Count or Switch or IF Statement, confused and need guidance

[safi]

Hi guys, 

I have the following calculated columns where based on the inventory levels, a recommendation is returned, 'Yes - Buy Review' or 'No - surplus available' now I want to establish a new measure or column, that simply caculates the total count of 'Yes Buy Review' for instance in the snapshot attached below, the first row has only 1 buy review for the 24 month segment, and the others are now,  the desired output based on this scenario will be 1. as in a horizontal count of buy reviews, but I am completely stumped as to how i go about, the only idea that comes to mind is to create another column with an if statement, that returns a  value of 1 in the case of 'Yes - Buy Review' or 0 in the case of 'No - surplus available' and then creating a 4th column that does a sum on aforemention columns to return a value.  Surely there must be a more efficient way of doing this. 

Any helpf here would be amazing! 

[amitchandak · Accepted solution]

@safi , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can try like

if([buy_review_Status_24] ="Yes - Buy Review",1,0) + if([buy_review_Status_3] ="Yes - Buy Review",1,0) + if([buy_review_Status+6] ="Yes - Buy Review",1,0)

or use switch true for multiple condition https://www.youtube.com/watch?v=gelJWktlR80

[safi]

@amitchandak  thank you so much for your reply and I am happy to report the if statement solution you provided is working like a charm! thank you so much. 

I just had a follow-up question based on best practices, do you recommend I create that as a measure or calculated column? what would be pros and cons going one way or the other? 

thank you so very much, I didnt know one could sum multiple IF statements, I bow to thee.

[amitchandak]

Calculated column add to the data, and increase load time. It will added as column in table. While measure is definition. Will exectued run time.

I refer this blog for more details - https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/