Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
webi2pbi
Regular Visitor

DAX Function

Hello,

 

webi2pbi_0-1692301964877.png

 

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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 )

vxinruzhumsft_0-1692325910422.png

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.

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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 )

vxinruzhumsft_0-1692325910422.png

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.

 

Thanks Yolo! This helps.

We will take it to the next steps

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.