If any one Value is Yes in one column, then replicate Yes in other column

[ritu24raj]

Hi - 

I have a list of accounts, I need to know if a particular account is a Sales Account or not based on the column which is "Sales Account".

If any account has one value as "Yes", I would need all Yes in the Yes/ No column. For Eg. - In the screenshot, Verizon has one line item as Yes, so I would require all Yes in the Yes/ No column.

Sales Account is an already created measure

Note - I am using SSAS >> Live connection w/ cube to Power BI.

[PaulDBrown]

@ritu24raj 

How are you splitting the rows which have the same ID, Account Name and Sales account values?

[ritu24raj]

@PaulDBrown - They are splited based on the year and quarter and there are few child accounts too. Hope this answers! 

[V-lianl-msft]

Hi @ritu24raj ,

What is the formula of "Sales Account"?

Try this measure:

Measure = CALCULATE([Measure 2],ALLEXCEPT('Table','Table'[ID],'Table'[Name]))

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

[ritu24raj]

Thanks @V-lianl-msft  I tweaked the formula and its running.

But thanks for the help 🙂

Regards,

Ritu Raj

[amitchandak]

@ritu24raj , Create a new column

if(countx(filter(Table, [Account Name] = earlier([Account Name]) && [Sales Account] ="Yes"),[Account Name])+0>0, "Yes","No")

[ritu24raj]

Hi @amitchandak - Thanks for this, as I mentioned I am connected to a cube and I cant use the EARLIER function. Additionally, I can only create measures and not columns.

[negi007]

@ritu24raj  I guess without creating a column, it would be difficult to achieve. If you can create a column, below solution would work for you.

[negi007 · Accepted solution]

@ritu24raj 

So here I created two calculated columns 

1.  Yes/No_Count = if ('Sales Account'[Sales Account]="Yes",1,0)

2. Yes_No_Final = if(CALCULATE(SUM('Sales Account'[Yes/No_Count]),ALLEXCEPT('Sales Account','Sales Account'[Account Name]))>0,"Yes","No")

[ritu24raj]

Hi @negi007 - It is not working either