Need Help with DAX for Multiple Columns with Multiple Equations

[Strongbuck]

I am trying to calculate a number using a formula.  Depending on what value is in the Description column, I want to multiple it by a different number.  I can get one statement to work but it does not like that I have filters on the different equations.

I searched and I do not see anything as complicated as I am trying to accomplish.  The table looks like:

Description	Apr 24	May 24	Jun 24	Jul 24	Aug 24
High Use Lawn Soil	0	0	0	0	0
Plant Bed Soil	0	0	0	0	0
Horticultural Subsoil	0	0	0	0	0
Sand Based Structural Planting Soil	0	0	0	0	0
Lightweight Plant Bed Soil	0	0	0	0	0
Bioretention Planting Soil	0	0	0	0	0
Meadow Soil	0	0	0	0	0
Crushed Stone for Bioretention Basin	0	0	0	0	0
Sand and Gravel for Reinforced Turf	0	0	0	0	0
Sand and Gravel for Bioretention Basin	0	0	0	0	0
Sand for Bioretention Basin	0	0	0	0	0
Sand over Structure	0	0	0	0	0
Sand for Subsurface Drainage	0	0	0	0	0
Sand-Compost Blend	0	0	0	0	0

This is easily written in Excel where the "Description" field is column A.  This Excel formula would be copied into each column.

=B2*0.4+B3*0.5+B4*0.67+B5*0.13+B6*0.75+B7*0.2

=C2*0.4+C3*0.5+C4*0.67+C5*0.13+C6*0.75+C7*0.2

ETC

I want the DAX formula to calcuate this expression for all columns.  The statement below is only using the April 2024 column.  I figured if I could get the statement to work, I could work on getting it to calculate the formula for all columns after that.  I tried a few things but I am so confused with the DAX syntax.

Base Wood =
CALCULATE (PRODUCTX('All_Projects', 'All_Projects'[Apr 24] * 0.4 ), FILTER ('All_Projects', 'All_Projects'[Description] = "High Use Lawn Soil") +
('All_Projects', 'All_Projects'[Apr 24] * 0.5 ), FILTER ('All_Projects', 'All_Projects'[Description] = "Plant Bed Soil") +
('All_Projects', 'All_Projects'[Apr 24] * 0.67 ), FILTER ('All_Projects', 'All_Projects'[Description] = "Horticultural Subsoil") +
('All_Projects', 'All_Projects'[Apr 24] * 0.13 ), FILTER ('All_Projects', 'All_Projects'[Description] = "Sand Based Structural Planting Soil")+ ('All_Projects', 'All_Projects'[Apr 24] * 0.75 ), FILTER ('All_Projects', 'All_Projects'[Description] = "Lightweight Plant Bed Soil") +
('All_Projects', 'All_Projects'[Apr 24] * 0.2 ), FILTER ('All_Projects', 'All_Projects'[Description] = "Bioretention Planting Soil")
) +0

An error message is being displayed - A function 'Filter' has been used in a TRUE/FALSE...

I wish I could get DAX Studio.  Any help would be appreciated.

[AlexisOlson · Accepted solution]

Try a measure like this:

Base Wood =
SUMX (
    'All_Projects',
    'All_Projects'[Apr 24]
        * SWITCH (
            'All_Projects'[Description],
            "High Use Lawn Soil",    0.40,
            "Plant Bed Soil",        0.50,
            "Horticultural Subsoil", 0.67
        )
)

You should really unpivot your data table so that different dates are not separate columns. See here:
https://learn.microsoft.com/en-us/power-query/unpivot-column