cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors