How to create a caluculated column using dax

[Yonas]

I have two columns name and retention days, I want to create a calculated column which checks" For each name check if there is a retention day value>30, if there is "Yes" else "No"

my sample data look like this

Name     Retention days

vc1           10

vc1           35

vc1           20

vc2           15

vc2           20

vc2           25

my expected result is 

Name     Retention days      isaDeleted

vc1           10                         Yes

vc1           35                         Yes

vc1           20                         Yes

vc2           15                         No

vc2           20                         No

vc2           25                         No

logic-If there is a retention day value >30 for vc1 then populate "yes" for each row in "isdeleted" column for vc1 even though there are some records for vc1 that are below 30. your help is apperciated

[HotChilli · Accepted solution]

Column = IF (CALCULATE(MAX(TableXX[Retention days]), ALLEXCEPT(TableXX, TableXX[Name])) > 30  , "Yes", "No")

[dax · Accepted solution]

Hi @Yonas , 

You also could try below M code to achieve this goal

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjNU0lEyNFCK1YGyjU0RbCOouBFIjSmCjSxuBBSPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, #"retention day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"retention day", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"max", each List.Max([retention day]), type number}, {"all", each _, type table [name=text, retention day=number]}}),
    Custom1 = Table.ReplaceValue(#"Grouped Rows", each  [max], each if [max]>30 then "Yes" else "No", Replacer.ReplaceValue, {"max"}),
    #"Expanded all" = Table.ExpandTableColumn(Custom1, "all", {"retention day"}, {"retention day"})
in
    #"Expanded all"

Best Regards,
Zoe Zhi

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

[HotChilli · Accepted solution]

Column = IF (CALCULATE(MAX(TableXX[Retention days]), ALLEXCEPT(TableXX, TableXX[Name])) > 30  , "Yes", "No")