Forum Discussion

Strongbuck's avatar
Strongbuck
Helper I
2 years ago
Solved

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:

 

DescriptionApr 24May 24Jun 24Jul 24Aug 24
High Use Lawn Soil00000
Plant Bed Soil00000
Horticultural Subsoil00000
Sand Based Structural Planting Soil00000
Lightweight Plant Bed Soil00000
Bioretention Planting Soil00000
Meadow Soil00000
Crushed Stone for Bioretention Basin00000
Sand and Gravel for Reinforced Turf00000
Sand and Gravel for Bioretention Basin00000
Sand for Bioretention Basin00000
Sand over Structure00000
Sand for Subsurface Drainage00000
Sand-Compost Blend00000

 

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.

DAX
DAX Equation
Filters
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    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