calculated column

[ArashZ]

Hi,

I have a table like the below:

There are some companies on the table and each one sells different products. As can be seen, in the table, if the product code is glasses or shipping or additional item, the plan frequency column is monthly; otherwise is yearly. I want to create a new plan frequency modified column in a way that if a car or pen or pencil exists for that company, all the plan frequencies for that company change to yearly. The new desired common is also shown.

Company Name	Plan frequency	Productcode
A	Monthly	glasses
A	Monthly	shipping
A	Yearly	car
A	Yearly	pen
B	Monthly	glasses
B	Yearly	pen
B	Yearly	pencil
B	Yearly	bottle
C	Monthly	glasses
C	Monthly	shipping
C	Yearly	car
C	Yearly	pen
C	Monthly	additional item

[wdx223_Daniel]

CalculatedColumn=VAR _c=Data[Company Name] RETURN IF(COUNTROWS(FILTER(Data,Data[Company Name]=_c&&Data[Productcode] IN {"car","pen","pencil"})),"Yearly",Data[Plan frequency])

[ArashZ]

@wdx223_Daniel 

Thank you, but the solution doesn't work. The table still shows some monthly values.

[v-easonf-msft]

Hi, @ArashZ 

If @wdx223_Daniel 's formula doesn't work, can you show relevant screenshots to further investigate what's causing the error?

Also, check if the bias in the results is caused by errors in the data itself.

Best Regards,
Community Support Team _ Eason

[AnthonyJoseph]

Hi @ArashZ 

I think this is what you are looking for...

Plan Frequency Modified = 
var _isinlist = CALCULATE(COUNT('Table'[Productcode]),CONTAINSROW({"car","pen","pencil"},'Table'[Productcode]),FILTER(ALLEXCEPT('Table','Table'[Company Name]),'Table'[Company Name] = EARLIER('Table'[Company Name])))
return
if(_isinlist >=1,"Yearly", "Monthly")

Hope this helps!

Thanks, 

AnthonyJoseph

[ArashZ]

@AnthonyJoseph 

Thanks, but I received this error.

A single value for the column company name in the table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation to get a single result.

[AnthonyJoseph]

Hi @ArashZ 
Would you be able to share the DAX that you tried so that I can see if there are any syntatical errors.
Thanks,
AnthonyJoseph

[AnthonyJoseph]

Hi @ArashZ 

Please can you provide data anonymising/removing the sensitive information.