Forum Discussion
Create a new column from another table field values
Hello everyone,
I have a question to create a new table as below.
I already have 'Table A' in my Power BI and want to get those boolean columns as value in Boolean Type in the new table, and get another column to count the number of true values.
Can anyone help me what DAX I need to use to get this new table in Power BI?
Hi Anonymous ,
The calculation logic that amitchandak given is right. But some details of the formulas need to correct. Please try this:
DAX:
new table = UNION( SUMMARIZE('Table', "Boolean Type" , "Boolean A","No of report", COUNTX(FILTER('Table', 'Table'[Boolean A] = "TRUE"), 'Table'[Boolean A])), SUMMARIZE('Table', "Boolean Type" , "Boolean B","No of report", COUNTX(FILTER('Table', 'Table'[Boolean B] = "TRUE"),'Table'[Boolean B])), SUMMARIZE('Table', "Boolean Type" , "Boolean C","No of report", COUNTX(FILTER('Table', 'Table'[Boolean C] = "TRUE"), 'Table'[Boolean C])), SUMMARIZE('Table', "Boolean Type" , "Boolean D","No of report", COUNTX(FILTER('Table', 'Table'[Boolean D] = "TRUE"), 'Table'[Boolean D])))M:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUQoJCnXFTsXqRCtFAjlujj7BrjhpkKoohE50RRCTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Report = _t, #"Boolean A" = _t, #"Boolean B" = _t, #"Boolean C" = _t, #"Boolean D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report", type text}, {"Boolean A", type logical}, {"Boolean B", type logical}, {"Boolean C", type logical}, {"Boolean D", type logical}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Report"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}) in #"Removed Columns"
3 Replies
- amitchandak
Super User
Anonymous , Better to unpivot in Power Query, and then Aggregate there.
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
In dax would summarize and union
new table =union(
summarize(Table, "Boolean Type" , "No of report", countx(filter(Table, Table[Boolean A] = "True") Table[Boolean A])),
summarize(Table, "Boolean Type" , "No of report", countx(filter(Table, Table[Boolean b] = "True") Table[Boolean B])),
summarize(Table, "Boolean Type" , "No of report", countx(filter(Table, Table[Boolean C] = "True") Table[Boolean C])),
summarize(Table, "Boolean Type" , "No of report", countx(filter(Table, Table[Boolean D] = "True") Table[Boolean D])))Change as per need
v-xuding-msftCommunity Support
Hi Anonymous ,
The calculation logic that amitchandak given is right. But some details of the formulas need to correct. Please try this:
DAX:
new table = UNION( SUMMARIZE('Table', "Boolean Type" , "Boolean A","No of report", COUNTX(FILTER('Table', 'Table'[Boolean A] = "TRUE"), 'Table'[Boolean A])), SUMMARIZE('Table', "Boolean Type" , "Boolean B","No of report", COUNTX(FILTER('Table', 'Table'[Boolean B] = "TRUE"),'Table'[Boolean B])), SUMMARIZE('Table', "Boolean Type" , "Boolean C","No of report", COUNTX(FILTER('Table', 'Table'[Boolean C] = "TRUE"), 'Table'[Boolean C])), SUMMARIZE('Table', "Boolean Type" , "Boolean D","No of report", COUNTX(FILTER('Table', 'Table'[Boolean D] = "TRUE"), 'Table'[Boolean D])))M:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUQoJCnXFTsXqRCtFAjlujj7BrjhpkKoohE50RRCTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Report = _t, #"Boolean A" = _t, #"Boolean B" = _t, #"Boolean C" = _t, #"Boolean D" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report", type text}, {"Boolean A", type logical}, {"Boolean B", type logical}, {"Boolean C", type logical}, {"Boolean D", type logical}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Report"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}) in #"Removed Columns"
