Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
So I have this table and i want to create a new column that display "Presented" if "Presented or Not" has both "Presented" and "Not Presented" for the same "ProductName", else just display normal result accordance to "Presented Or Not" column . I am not sure how to do this so please help.
Solved! Go to Solution.
Hi @dduc2310 ,
1. Whole M In Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQooSi1OzStJTVGK1YGI+OWXKFAmiirihFUddlFnDN3OxKiLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, #"Presented Or Not" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectName", type text}, {"Presented Or Not", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectName"}, {{"Count", each _, type table [ProjectName=nullable text, Presented Or Not=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Table.Contains([Count],[Presented Or Not="Presented"]) then "Presented" else "Not Presented"),
#"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Presented Or Not"}, {"Presented Or Not"})
in
#"Expanded Count"
2. try the following DAX formula:
Result =
var _t=DISTINCT( SUMMARIZE('Table','Table'[ProjectName], 'Table'[Presented Or Not]))
return IF( COUNTX(FILTER(_t,[ProjectName]=EARLIER('Table'[ProjectName])),[Presented Or Not])=2,"Presented",[Presented Or Not])
Below is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dduc2310 ,
1. Whole M In Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQooSi1OzStJTVGK1YGI+OWXKFAmiirihFUddlFnDN3OxKiLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, #"Presented Or Not" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectName", type text}, {"Presented Or Not", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectName"}, {{"Count", each _, type table [ProjectName=nullable text, Presented Or Not=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Table.Contains([Count],[Presented Or Not="Presented"]) then "Presented" else "Not Presented"),
#"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Presented Or Not"}, {"Presented Or Not"})
in
#"Expanded Count"
2. try the following DAX formula:
Result =
var _t=DISTINCT( SUMMARIZE('Table','Table'[ProjectName], 'Table'[Presented Or Not]))
return IF( COUNTX(FILTER(_t,[ProjectName]=EARLIER('Table'[ProjectName])),[Presented Or Not])=2,"Presented",[Presented Or Not])
Below is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is better done in DAX. Use COUNTROWS(VALUES([ProductName])) and test if it is more than 1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.