Create a new column from another table field values

[Anonymous]

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? 

[v-xuding-msft · Accepted solution]

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"

[Anonymous]

thanks a lot!  this helps me understand much better!

[amitchandak]

@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