DAX Function

[webi2pbi]

Hello,

So I have a scenario where for a certain material (column B), we have a corresponding standard_status_cd (column D), however we have a scenario where the business wants a desired result of (Column E), if there is a material type of ZSAM, we want to list every material under the same specification (column A) as having a value of "2", even if it doesn’t contain a “2” value for each material record.

Any idea what DAX function would worsk best to accomplish this?

thanks

[Anonymous · Accepted solution]

Hi @webi2pbi 

1.You can put the following code to Advanced Editor in powr query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcwNDQ0UNJRMja0MDQ0NzAwsgRyPBx9nICUkVKsTrQSRBIkZWFgAFIaFezoiyZrYA7S5uYaFAKkkMTNzMyxilsYmeMwCGwDmgYTDINiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Specification = _t, Material = _t, #"Mat Type" = _t, Standard = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Specification", Int64.Type}, {"Material", Int64.Type}, {"Mat Type", type text}, {"Standard", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Specification"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Specification"}, {{"Count", each _, type table [Specification=nullable number, Material=nullable number, Mat Type=nullable text, Standard=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Count][Mat Type],"ZSAM") then List.Max([Count][Standard]) else null),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Material", "Mat Type", "Standard"}, {"Material", "Mat Type", "Standard"})
in
    #"Expanded Count"

 2.You can create a column in table

Column =
VAR a =
    COUNTROWS (
        FILTER (
            'Table (2)',
            [Specification] = EARLIER ( 'Table (2)'[Specification] )
                && [Mat Type] = "ZSAM"
        )
    )
VAR b =
    MAXX (
        FILTER (
            'Table (2)',
            [Specification] = EARLIER ( 'Table (2)'[Specification] )
                && [Mat Type] = "ZSAM"
        ),
        [Standard]
    )
RETURN
    IF ( a > 0, b )

Best Regards!

Yolo Zhu

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

[webi2pbi]

Thanks Yolo! This helps.

We will take it to the next steps