Forum Discussion
I need help modifying an existing DAX formula
// One way of doing this is:
Media Due =
var MonthNumber = dimstartdate[monthnumber]
var YearNumber = dimstartdate[YearNumber]
var MediaType = T[Media Type] // adjust this to your model!
var Output =
if( YearNumber = 2023,
switch(
MediaType,
"Digital",
switch(
MonthNumber,
1, "12/14/2022",
2, "1/19/2023",
3, "2/15/2023",
4, "3/23/2023",
5, "4/20/2023",
6, "5/17/2023",
7, "6/21/2023",
8, "7/20/2023",
9, "8/23/2023",
10, "9/20/2023",
11, "10/18/2023",
12, "11/21/2023"
),
"Direct Mail",
switch(
... -- similar to above
)
)
return
Output
// ... but it'd be even better to create a driving table
// that would store a mapping between Year, MediaType,
// MonthNumber and the output values. This then could be
// stored outside the report in some source system and
// adjusted without even having to touch the DAX.
// The above solution is the first naive one but I encourage
// you to create the more robust one with the driving table.
- krichmond3 years ago
Helper IV
daXtreme - Thank you for sending this over. I tried using the formula approach to start and when I went to save it, I got the following error message. I do notice that "Media Type" is grey and says that it cannot be found. However, I just confirmed that it is indeed in my report (see screenshot). Any idea what is causing this to fail?
The syntax for 'return' is incorrect. (DAX(var MonthNumber = dimstartdate[monthnumber]var StartDate = dimstartdate[Yearnumber]var MediaType = T[Media Type]var Output = if( YearNumber = 2023, switch( MediaType, "Digital", switch( MonthNumber, 1, "12/14/2022", 2, "1/19/2023", 3, "2/15/2023", 4, "3/23/2023", 5, "4/20/2023", 6, "5/17/2023", 7, "6/21/2023", 8, "7/20/2023", 9, "8/23/2023", 10, "9/20/2023", 11, "10/18/2023", 12, "11/21/2023" ), "Direct Mail", switch( MonthNumber, 1, "12/7/2022", 2, "12/7/2022", 3, "1/6/2023", 4, "2/13/2023", 5, "3/14/2023", 6, "4/10/2023", 7, "5/11/2023", 8, "6/9/2023", 9, "7/10/2023", 10, "8/11/2023", 11, "9/11/2023", 12, "10/4/2023" ),return Output)).
- daXtreme3 years ago
Solution Sage
Adjust the code I gave you to the names of the objects you've got in your model. That's very easy to do. Really.