Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Strongbuck
Helper I
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:

 

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.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
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

View solution in original post

1 REPLY 1
AlexisOlson
Super User
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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors