dax equation

3 Topics

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.
Solved
Strongbuck
2 years ago Place DAX Commands and Tips
829Views
0likes
1Comment
Dax For TopNCustomers
Hey Everyone, I have two tables first 'Sales Data' from which I am focusing on columns 'Order Date', 'Shipping Mail Id' and 'Order Number' The second table is Calendar which is linked with the Sales Data table on the basis Calendar[ Date] and Sales Data[Order Date]. I want to know about the Top 5 customers' Order Count Grand total of every month. for example I want a table like this. YEAR MONTH ORDER COUNT 2023 JULY 88 2023 JUNE 108 2023 APRIL 107 And the order count come from another table: top 5 customer of month July Shipping Mail Id Order Count [email protected] xx [email protected] xx [email protected] xx [email protected] xx [email protected] xx grand total 88
Sonam1301
3 years ago Place DAX Commands and Tips
383Views
0likes
1Comment
Query on DAX Equation
Kindly assist with the below; Consider the table below: Client ID Loan Product ID 1 Education 2 Crop 3 Education 4 Animal 1 Crop 1 Water 4 Education I would like to identify client IDs that got "Education" loan product ID but did not get any other loan product ID, and also to identify client IDs that got "Education" loan product ID plus any other loan product ID. Please help Nested Filter DAX Query
Solved
hosea_chumba
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
5Comments