Forum Discussion
Helper IVI 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:
8 Replies
v-yadongf-msftCommunity 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.
krichmondv-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?
daXtremeSolution 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.- 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.