cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Need Help with DAX for Multiple Columns with Multiple Equations

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.

1 ACCEPTED SOLUTION
Super User

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

Super User

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