DAX to Calculate a Filtered Set of Form Data - Not in Columns

[Strongbuck]

I'm trying to figure out how to construct a filtered DAX statement to calculate the Percent Recycled field multipled by the Waste Quantity field (Highlighted in Blue).  The data is being captured in a form so the data is not in columns.  I have tried Filter Variables, Filtered SELECTCOLUMNS and Transposing the columns/rows.

Each time, I don't get the correct answer.  Can someone please point me in the right direction? 

I need the calculation to sum the data by Month and Year so I created two fields as:

Recycled_Month = IFERROR (MONTH ('Native Forms [Answer]), BLANK())

Recycled_Year = IFERROR (YEAR ('Native Forms [Answer]), BLANK())

Because the Answer field is text, I had to convert the two fields to an Integer.   

Covert_Answers to Number = IFERROR(CONVERT('Native Forms'[Answer], INTEGER), BLANK())

I only need to report this calculation when the Answer to the Debris Type is "Comingled" or "Comingled Debris" (Highlighted in Yellow).

Any help would be appreciated!

[Strongbuck]

Form #	Question	Answer	Recycled_Month	Recycled_Year	Covert_Answers to Numbers	Project Name
3	Debris Type	Wood				Sample Project 1
3	Date of Recycle	7/1/2024	7	2024		Sample Project 1
3	Rx Destination	Kentucky Dump				Sample Project 1
3	Waste Quantity	10			10	Sample Project 1
6	Debris Type	Comingled Debris				Sample Project 1
6	Hauler					Sample Project 1
6	Percent Recycled	75			75	Sample Project 1
6	Rx Destination					Sample Project 1
6	Rx Other Destination					Sample Project 1
6	Waste Quantity	10			10	Sample Project 1
7	Debris Type	Bottles/Cans				Sample Project 1
7	Hauler	Everyone's Handyman				Sample Project 1
7	Percent Recycled - Other	100			100	Sample Project 1
7	Rx Destination	Metal Management				Sample Project 1
7	Date of Recycle	7/1/2024				Sample Project 1
7	Waste Quantity	50			50	Sample Project 1
88	Debris Type	Comingled				Sample Project 2
88	Hauler	KY Wrecking and Recycling				Sample Project 2
88	Percent Recycled	75			75	Sample Project 2
88	Rx Destination	Land Recycling				Sample Project 2
88	Date of Recycle	6/1/2024	6	2024		Sample Project 2
88	Waste Quantity	50			50	Sample Project 2

[adudani]

Hello,

Kindly provide the sample input/output in a usable format (excel, csv, table etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

Thanks,

[Strongbuck]

Hi,

I've added the data as a table.  I tried to add the Excel spreadsheet but it displayed an error that that format is not supported.

Thanks for your help!

[adudani · Accepted solution]

hi @Strongbuck 

in the power query editor, create a blank query. Copy and paste the code below into the advanced editor: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVRhT4MwEP0rFz7PDNDBPjtMlszFOU2ImftQ4Zw4aEkpRv69xzaBWAeMBML10nf37r2Wzca4NkaGh28yyuC5SJFWvhAhfarniSVpjLCS4hMDBZaxHZ1gTCGId1hjUARxCXXH1tg27ZsypPcUttVYf4OHmYo4U5HglFggV3mwL8DLk7QXDZ9lxOMxZ1xFqqCEZVagQ/gv0tHmnokk4rsYQzjmO5uXJeYsj1E2t7btXqEMaL5fxUqZ3UkFO4RnsZpSvToS6kF9oByCHSasqwl7K5SKMRvPGO8W1W2KeveFshAcX3PTtN0M5oyHRcJ4ryp/xYaroxQH9s1JWkfRZF+iYjEsGWc7TKh+Ly6tV6UbrjkxqflPztKfTs+f8Za2dg2ujFi8gC8x2BMYyIPTHLTqV+fyc98Aaw7cD2CgG+DUBjht/6pGkUttIOj2Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form #" = _t, Question = _t, Answer = _t, Recycled_Month = _t, Recycled_Year = _t, #"Covert_Answers to Numbers" = _t, #"Project Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form #", Int64.Type}, {"Question", type text}, {"Answer", type text}, {"Recycled_Month", Int64.Type}, {"Recycled_Year", Int64.Type}, {"Covert_Answers to Numbers", Int64.Type}, {"Project Name", type text}}),
    
    //Filtered rows to where question contains recycle ,waste or debris
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Question], "Recycle") or Text.Contains([Question], "Waste") or Text.Contains([Question], "Debris")),
    
    //grouped rows by form # and project name
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Form #", "Project Name"}, {{"Data", each _ }}),
    
    // filter the grouped table for question and answer columns
    FilteredTable = Table.AddColumn(#"Grouped Rows", "FilteredTable", each Table.SelectColumns( [Data] , {"Question","Answer"} )),
    
    //remove originally grouped data
    #"Removed Columns" = Table.RemoveColumns(FilteredTable,{"Data"}),
    
    //expand the table
    #"Expanded FilteredTable" = Table.ExpandTableColumn(#"Removed Columns", "FilteredTable", {"Question", "Answer"}, {"Question", "Answer"}),
    
    // create a merged anchor column to pivot on
    #"Project Name -- Form #" = Table.AddColumn(#"Expanded FilteredTable", "Project Name -- Form #", each Text.Combine({[Project Name], Text.From([#"Form #"], "en-CA")}, " --"), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Project Name -- Form #",{"Form #", "Project Name"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Question]), "Question", "Answer"),
    
    // final debris filter condition and answer calculation 
    #"Filtered Debris Type contains Comingle" = Table.SelectRows(#"Pivoted Column", each Text.Contains([Debris Type], "Comingle")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Debris Type contains Comingle",{{"Debris Type", type text}, {"Date of Recycle", type date}, {"Waste Quantity", type number}, {"Percent Recycled", type number}, {"Percent Recycled - Other", type number}}),
    Answer = Table.AddColumn(#"Changed Type1", "Answer", each [Waste Quantity]*[Percent Recycled])
in
    Answer

does the output below meet the requirement?