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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krichmond
Helper III
Helper III

I need help modifying an existing DAX formula

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())
8 REPLIES 8
daXtreme
Solution Sage
Solution Sage

 

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

 

@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?

 

Screenshot 2022-11-03 061403.png

 

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

 

Screenshot 2022-11-03 060912.pngScreenshot 2022-11-03 060942.png

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.

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

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

@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?

 

Screenshot 2022-11-08 090004.png

 

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
Community Support
Community Support

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.

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors