I need help modifying an existing DAX formula

[krichmond]

I need help modifying an existing DAX formula that sits under a field called "Media Due" so that it looks at this field and returns different values depending on what the value of that field is. How would I combine this into single DAX formula that accomodates both different scenarios?

If the "Media Type" field is "Digital" than the "Media Due" field should have the following dates:

Media Due = switch(true(),

dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/14/2022",

dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "1/19/2023",

dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "2/15/2023",

dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "3/23/2023",

dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "4/20/2023",

dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "5/17/2023",

dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "6/21/2023",

dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "7/20/2023",

dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "8/23/2023",

dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "9/20/2023",

dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "10/18/2023",

dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "11/21/2023",

BLANK())

If the "Media Type" field is "Direct Mail" than the "Media Due" field should have the following dates:

Media Due = switch(true(),

dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/7/2022",

dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "12/7/2022",

dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "1/6/2023",

dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "2/13/2023",

dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "3/14/2023",

dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "4/10/2023",

dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "5/11/2023",

dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "6/9/2023",

dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "7/10/2023",

dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "8/11/2023",

dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "9/11/2023",

dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "10/4/2023",

BLANK())

[daXtreme]

// 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.

[krichmond]

@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)).

[daXtreme]

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.

[krichmond]

@daXtreme - Easy for a super user I am sure it is easy. However, I am brand new to Power BI. Any assisntance you can provide in tweaking the DAX would be greatly appreciated,.

[daXtreme]

Well, as I said, it's easy to do. First, the intellisense tells you that you don't have a variable called YearNumber. But you do have StartDate which in fact is the year number, right? So, you have to decide on one of them and then replace the other. Second, since you did not tell us what your table was called, I used the general term "T" and also the column name "Media Type." You have to adjust this to your model...

[krichmond]

@daXtreme - I ended up trying it this way and there aren't any errors. However, when looking at the report itself, nothing is populating in the "Media Due" field which contains the DAX formula. Any idea why this is happening?

Media Due = switch(true(),

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/14/2022",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "1/19/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "2/15/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "3/23/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "4/20/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "5/17/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "6/21/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "7/20/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "8/23/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "9/20/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "10/18/2023",

MAX(mv_perfex[mediatype]) = "Digital" && dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "11/21/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/7/2022",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "12/7/2022",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "1/6/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "2/13/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "3/14/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "4/10/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "5/11/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "6/9/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "7/10/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "8/11/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "9/11/2023",

MAX(mv_perfex[mediatype]) = "Direct Mail" && dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "10/4/2023",

BLANK())

[v-yadongf-msft]

Hi @krichmond ,

The logic for these two measures looks the same. Do you want to create a measure based on the type of field? Maybe you can try:

IF( ISNUMBER(dimstartdate[monthnumber]) = TRUE(),
switch(true(),
dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/14/2022",
dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "1/19/2023",
dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "2/15/2023",
dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "3/23/2023",
dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "4/20/2023",
dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "5/17/2023",
dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "6/21/2023",
dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "7/20/2023",
dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "8/23/2023",
dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "9/20/2023",
dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "10/18/2023",
dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "11/21/2023",
BLANK()),
 
switch(true(),
dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/7/2022",
dimstartdate[monthnumber] = 2 && dimstartdate[Yearnumber] = 2023, "12/7/2022",
dimstartdate[monthnumber] = 3 && dimstartdate[Yearnumber] = 2023, "1/6/2023",
dimstartdate[monthnumber] = 4 && dimstartdate[Yearnumber] = 2023, "2/13/2023",
dimstartdate[monthnumber] = 5 && dimstartdate[Yearnumber] = 2023, "3/14/2023",
dimstartdate[monthnumber] = 6 && dimstartdate[Yearnumber] = 2023, "4/10/2023",
dimstartdate[monthnumber] = 7 && dimstartdate[Yearnumber] = 2023, "5/11/2023",
dimstartdate[monthnumber] = 8 && dimstartdate[Yearnumber] = 2023, "6/9/2023",
dimstartdate[monthnumber] = 9 && dimstartdate[Yearnumber] = 2023, "7/10/2023",
dimstartdate[monthnumber] = 10 && dimstartdate[Yearnumber] = 2023, "8/11/2023",
dimstartdate[monthnumber] = 11 && dimstartdate[Yearnumber] = 2023, "9/11/2023",
dimstartdate[monthnumber] = 12 && dimstartdate[Yearnumber] = 2023, "10/4/2023",
BLANK())
)

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[krichmond]

@v-yadongf-msft - The logic is slightly different. If the "Media Type" field is "Digital" than the "Media Due" field will have a certain set of dates that apply. However, if the "Media Type" field is "Direct Mail" than the "Media Due" field will have another set of dates that apply. Would the calculation you provided accomodate this variation?