Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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.
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.
Thanks Yolo! This helps.
We will take it to the next steps
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |